Transform data from various cells into a single column

[Steve]

Hi,

I'm in a major jam here and have been looking through the groups most
of the afternoon for help...but to no avail.

I need to be able to take data that is spread out through a worksheet,
with a number of blank cells in between and convert it all to a single
row. I have 1500 rows of this data that look something like the
following:

A B C D E F
1 CU281 CU282 CU283 CU284 CU285 CU286

2 CA952

3 CU024

4 CU281 CU282 CU283 CU284 CU285 CU286

5 CA952 CA954 CA977

I need:
A
1 CU281
2 CU282
3 CU283
4 CU284
5 CU285
6 CU286
7 CA952
8 CU024 get the picture?

Any help would be greatly appreciated...


Regards,

Steve

 

[GB]

There are lots of ways of doing this. I doubt this is the most elegant but
it should work okay. I have assumed that all your data is in columns A to K.

In cell M1 put the letter A, in cell M2, put B, etc down to J in M10. In
M11, put = M1. Copy the formula in M11 all the way down the column to M20000
or so.

In Cells N1 to N10 put 1. In N11, put = N1 +1. Copy that down to N20000 or
so.

In L1 put = Indirect(M1&N1). Copy that down to L20000.

Column L should now have your data interspersed with lots of zeros. Use Data
..... Filter ... Autofilter to hide the zeros.

That's it.

Geoff

 

[Steve]

(e-mail address removed) (Steve) wrote in message
thanks alot, I tried it and it worked fine, steve