Moving data from one worksheet to another
I have one worksheets with multiple account codes listed in Col A,
description in Col B and amounts listed in Col C, D, E & F. Each account code does not necessary have an amount assoicated with it, however some accounts have amounts in multiple columns. I would like to have a summary sheet which moves only lines with amounts and has a separate line for each amount. I'm not sure I'm explaining correctly, how do you enter a sample worksheet without the spacing going haywire so I can give a better example? |
Moving data from one worksheet to another
|
Moving data from one worksheet to another
Wondering whether you read the reply below to your other similar looking
post in .newusers, http://tinyurl.com/3xnl8o --------------- 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 --- |
All times are GMT +1. The time now is 04:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com