ExcelBanter - stacking multiple column data into a single column

Here's a formulas play which delivers the required stacking ..

Assuming source data is in cols A to C, from row1 down,
with possible blank cells in between cells with data

Put in D1: =IF(A1="","",ROW())

Put in E1: =IF(B1="","",ROW()+MAX(D:D))
Copy E1 to F1

Put in G1:
=IF(ROW(A1)COUNT(D:D),IF(ROW(A1)COUNT(D:E),IF(RO W(A1)COUNT(D:F),"",INDEX(C:C,MATCH(SMALL(F:F,ROW( A1)-COUNT(D:E)),F:F,0))),INDEX(B:B,MATCH(SMALL(E:E,ROW (A1)-COUNT(D:D)),E:E,0))),INDEX(A:A,MATCH(SMALL(D:D,ROW (A1)),D:D,0)))

Then just select D1:G1, copy down to the very last row of data within cols A
to C. Col G will deliver the required results, with data stacked in sequence:
data from col A first, followed by data from col B, then data from col C, all
neatly bunched at the top w/o any intervening blank cells.

If needed, just freeze col G with an "in-place":
Copy Paste Special Check "Values" OK
Then clean up by deleting cols D to F
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Chubby" wrote:
I am Working with excel 2000. Weekly I generate data that is entered into
multiple columns on a worksheet. This data is duplicated in multiple
worksheets. This data is in 3 columns that vary in length from week to week.
Is it possible (for lack of a better word) to automatically transpose this
array of data into a single column of data with no spaces in these different
worksheets. I dont want to use the copy and paste functions and manually
create this single column. This data is not dynamic and is fixed once its is
entered. Thanks for any help
Mr. Chubby