Moving data from one worksheet to another

T

tjvols

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.
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top