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?

Upload it to
http://www.freefilehosting.net

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