One big column to lots of small columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee - 30 Jun 2006 18:35 GMT

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!

Reply to this Message

CLR - 30 Jun 2006 18:51 GMT

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

> Howdy Excel experts,
>
[quoted text clipped - 5 lines]
>
> I hope this makes sense. Thanks for your help!

Reply to this Message

Lee - 30 Jun 2006 20:50 GMT

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

> 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

Reply to this Message

Lee - 30 Jun 2006 20:55 GMT

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.

> Hi Chuck,
>
[quoted text clipped - 13 lines]
> > Vaya con Dios,
> > Chuck, CABGx3

Reply to this Message

RobertVA - 30 Jun 2006 21:43 GMT

> I should mention that each numerical value has its own cell, so the
> original data looks something like this:
[quoted text clipped - 29 lines]
>>> Vaya con Dios,
>>> Chuck, CABGx3

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

Reply to this Message

Lee - 30 Jun 2006 22:30 GMT

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.

> Try this:
>
[quoted text clipped - 16 lines]
> 7 15
> 8 16

Reply to this Message

RobertVA - 30 Jun 2006 23:40 GMT

> Robert,
>
[quoted text clipped - 6 lines]
> Hopefully the answer is somewhere out there, but if it isn't, this will
> make things a bit easier.

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.

Reply to this Message

Lee - 01 Jul 2006 00:00 GMT

I'll look into this.

Thanks for your help with this pesky problem!

> > Robert,
> >
[quoted text clipped - 12 lines]
> "^p^p") until all the gaps are dual paragraph marks, save as text and
> import into Excel again.

Reply to this Message