One big column to lots of small columns

L

Lee

Howdy Excel experts,

I have one big column of data, each chunk of values separated by a
space of blank cells. I'd like to take all the data chunks and put
them each in their own column, beginning at the top of the worksheet.
Basically taking one long column of data and transforming it into a
shorter, wider worksheet.

I hope this makes sense. Thanks for your help!
 
C

CLR

The feature that will do as you ask is Data > TextToColumns > using space as
the delimiter...........try it first on only a copy of your data.........

Vaya con Dios,
Chuck, CABGx3
 
L

Lee

Hi Chuck,

I don't think this is the solution I am looking for. This (as far as I
can tell) takes a cell of text and splits it up into separate columns.
My data is numeric, in one column, with 'gaps' in the column of blank
cells. I need to take the non-blank groups of numerical data and give
each one its own column at the top of the worksheet.

Thanks,

Lee
 
L

Lee

I should mention that each numerical value has its own cell, so the
original data looks something like this:

12
12
13


14
15
16

With about 4000 values.

I would like a solution to (for the example above) create 2 columns of
data, each containing the three respective cells.
 
R

RobertVA

Lee said:
I should mention that each numerical value has its own cell, so the
original data looks something like this:

12
12
13


14
15
16

With about 4000 values.

I would like a solution to (for the example above) create 2 columns of
data, each containing the three respective cells.
Click to expand...
Try this:

Fill formulas in b1:b3 down AND to the right. Note that each cell
obtains value from the cell 5 rows down and one column to the left. Each
set of numbers will end up being repeated one column farther to the
right as it is repeatedly reproduced from the bottom of the sheet.

If you don't like having all the extra at the bottom, reference rows 1
through 3 on another worksheet.

A B

1 12 =A6
2 12 =A7
3 13 =A8
4
5
6 14
7 15
8 16
 
L

Lee

Robert,

Thanks for the tip, this is a good idea. However, the amounts of blank
cells separating chunks of data is varied, so this method isn't quite
the solution I'm looking for. I am trying to avoid manually sifting
through data and deleting the data sets that don't belong in the
columns.

Hopefully the answer is somewhere out there, but if it isn't, this will
make things a bit easier.
 
R

RobertVA

Lee said:
Robert,

Thanks for the tip, this is a good idea. However, the amounts of blank
cells separating chunks of data is varied, so this method isn't quite
the solution I'm looking for. I am trying to avoid manually sifting
through data and deleting the data sets that don't belong in the
columns.

Hopefully the answer is somewhere out there, but if it isn't, this will
make things a bit easier.
Click to expand...

Only thing I can think of to get around that would be to "print" the
data to a generic text file, import that text into Word, repeatedly
replace triple paragraph marks with double ones (replace "^p^p^p" with
"^p^p") until all the gaps are dual paragraph marks, save as text and
import into Excel again.
 
Top