Convert data from multiple columns/rows into one column - help!

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi--

I need some help converting data from multiple rows/columns into one
column. My data looks like this (numbers starting on row 3):
BA BB BC BD BE
2-7-484 2-7-11702 2-7-9690
2-2-9692 2-2-9691 2-2-10289 2-2-8
2-14-4911
2-5-11766 2-5-10638 2-5-11236

and I would like it to look like this in another sheet:

2-7-484
2-7-11702
2-7-9690
2-2-9692
2-2-9691
2-2-10289
2-2-8
2-14-4911
2-5-11766
2-5-10638
2-5-11236

I have a variable number of columns/rows where this data is. But I can
have a cell that holds the number of columns and rows. I would like
blank cells to be skipped.

Any suggestions?

Thanks,
Jason
 
Here's one approach which delivers it all, dynamically ..

Assume source data in Sheet1, cols BA to BE (5 cols), data from row1 down

In another sheet,

a. Let's just extract the 5 source cols into 1 single col
Put in A1:
=OFFSET(Sheet1!$BA$1,INT((ROWS($1:1)-1)/5),MOD(ROWS($1:1)-1,5))
Copy A1 down as far as required to exhaust the source data (until zeros
appear continuously, signalling exhaustion). The number of rows to copy down
will be the total number of cells in the source range. Eg: if the source
range is a 5 col x 100 rows range, you need to copy down at least: 5 x100 =
500 rows.

The "5" in the INT & MOD parts is the 5 cols in the source (adapt this to
suit the number of source cols). "BA1" is the anchor cell in the source, ie
the top left, first cell in the source to be extracted (adapt as required).

b. Then, to "remove" all the zero lines that's in col A & float all the
results up
Put in B1: =IF(A1=0,"",ROW())
Put in C1: =IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Select B1:C1, copy down to col A's extent
Col C will return the final desired results, all neatly bunched at the top
(Hide away cols A & B if desired)
 
Jason said:
Hi--

I need some help converting data from multiple rows/columns into one
column. My data looks like this (numbers starting on row 3):
BA BB BC BD BE
2-7-484 2-7-11702 2-7-9690
2-2-9692 2-2-9691 2-2-10289 2-2-8
2-14-4911
2-5-11766 2-5-10638 2-5-11236

and I would like it to look like this in another sheet:

2-7-484
2-7-11702
2-7-9690
2-2-9692
2-2-9691
2-2-10289
2-2-8
2-14-4911
2-5-11766
2-5-10638
2-5-11236
Click to expand...

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayUniques(ArrayReshape(BA3:BD6, Rows(BA3:BD6)*columns(BA3:BA6),1)),
array entered into a column sufficiently long to accommodate the output.

Alan Beban
 
Back
Top