Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving data from one worksheet to another
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving data from one worksheet to another
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tjvols" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving data from one worksheet to another via a Macro! | Links and Linking in Excel | |||
Moving chart data to the same worksheet | Charts and Charting in Excel | |||
moving a worksheet..? | Excel Discussion (Misc queries) | |||
Moving data from from one worksheet to another | Excel Worksheet Functions | |||
Moving Data to Another Worksheet | Excel Discussion (Misc queries) |