Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
stacking multiple column data into a single column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function/sum function | Excel Discussion (Misc queries) | |||
combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Return Single Row of Numeric Data to Single Column | Excel Worksheet Functions | |||
how to convert multiple columns of data into one single column? | Excel Worksheet Functions |