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

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
 
M

Max

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

Alan Beban

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top