Moving data from one worksheet to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjvols - 13 Mar 2008 16:51 GMT

I am trying to move data from one worksheet to another - DATA SHEET
-

COST CODE    DESCRIPTION    M    S    O    L
010000    Labor Summary                5,000
011700    Burden              1,000
011750    General Liability             500
011775    Data Processing
011900    Equipment Repairs     1,000     4,500
012000    Utilities         200
012100    FLC Equipment             5,000
012200    Non-Owned Equipment Rental          6,000
012220    Toilet Rental     1,190

This is how I want the result to look - SUMMARY SHEET

Cost Code    CT    Description    Amount
010000    L    Labor Summary    5,000
011700    O    Burden      1,000
011750    O    General Liability     500
011900    M    Equipment Repairs     1.000
011900    S    Equipment Repairs     4.500
012000    S    Utilities     200
012100    O    FLC Equipment     500
012200    O    Non-Owned Equipment Rental      6,000
012220    M    Toilet Rental 11,900

Basically it moves only lines with data (ie. 011775 - Data Processing
does not have an amount associated with it so it does not show on
summary sheet). But if line has entries in two columns it moves over
in two lines with the "CT" associated (equipment repairs). Hope I
have explained ok. I have looked at several posts and nothing quite
fits what I need. Any help would be greatly appreciated.

Reply to this Message

Max - 14 Mar 2008 05:27 GMT

Here's a way to get the required summary
illustrated in this sample:
http://www.freefilehosting.net/download/3dd3b
Rearranging data automatically.xls

Source data assumed in sheet: Data, cols A to F, data from row2 down (as
posted)

In a new sheet: x,
In A2: =OFFSET(Data!A$2,INT((ROWS($1:1)-1)/4),)
In B2: =INDEX(Data!$C$1:$F$1,MOD(ROWS($1:1)-1,4)+1)
In C2: =OFFSET(Data!B$2,INT((ROWS($1:1)-1)/4),)
In D2: =OFFSET(Data!C$2,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))
In E2: =IF(D2=0,"",ROW())
Select A2:E2, copy down until zeros appear in col A, signalling exhaustion
of source data extract. The "4" used in the MOD & INT parts correspond to
the number of "CT" items (M,S,O,L) that's in "Data".

Then in sheet: Summary,
just place this in A2:
=IF(ROWS($1:1)>COUNT(x!$E:$E),"",INDEX(x!A:A,SMALL(x!$E:$E,ROWS($1:1))))
Copy A2 to D2, fill down to cover the max expected extent of data. You'd get
exactly the summary results that you seek.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I am trying to move data from one worksheet to another - DATA SHEET
> -
[quoted text clipped - 29 lines]
> have explained ok. I have looked at several posts and nothing quite
> fits what I need. Any help would be greatly appreciated.

Reply to this Message

Rate this thread: