Need help converting 1 column of data to several columns

D

dhooie

First of all, thanks up front for any help.

I am a simple Excel user who desperately needs to convert a singl
column of data that I imported from a text file that has several row
into multiple columns.

i.e: My data looks like this:

SMITH
Pentium II
400
128MB
JONES
Celeron
1000
256MB
RICHARDSON
Pentium 4
2200
1028

I'd like to convert it to this:

SMITH Pentium II 400 128MB
JONES Celeron 1000 256MB
RICHARDSON Pentium 4 2200 1028MB

There are several rows of data all pretty much grouped that way. Som
"groups" have extra rows of info, like serial # etc. What I'd like t
do is convert all the groups to a single row of data over multipl
columns and then shift all the new rows so that their respectiv
"fields" line up in the same column, but I can do that last par
manually.

What I really need to save me some time is a macro of some kind to ge
this single column of data into the format like I've shown above.

Also, I know NOTHING about Macros. (I'm not even sure I know how t
run one.) so please be gentle in your explanations and use little word
with as few syllables as possible.

Thanks so much in advance!

David Hooi
 
R

Ron de Bruin

Hi

If the number of rows are not the same for each name then this is difficult to do
 
R

Ron de Bruin

This is a example that transpose the data in Column A in blocks of four
in column B:E

Sub test()
Dim a As Long
Dim b As Long
Dim Rng As Range
Dim dest As Range
For a = 1 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row Step 4

Set Rng = ActiveSheet.Range(Cells(a, 1), Cells(a + 3, 1))
Set dest = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Offset(1)
Set dest = dest.Resize(Rng.Columns.Count, Rng.Rows.Count)
dest.Value = Application.Transpose(Rng)
Next
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Ron de Bruin said:
Hi

If the number of rows are not the same for each name then this is difficult to do
Click to expand...
 
A

Alan Beban

Here's another approach if the functions in the freely downloadable file
at http://home.pacbell.net/beban are available to your workbook; it
assumes that your single-column data is in a range named "dataRange":

Dim rng As Range
Set rng = Range("dataRange")
k = rng.Count / 4
Range("B1:E" & k).Value = ArrayReshape(rng, k, 4)

Alan Beban
 
R

Robert McCurdy

I didn't see any reply to this David. You can send me the file and I'll see what I can do if you are still in a pickle?

Regards Robert
 
Top