Need help converting 1 column of data to several columns

  • Thread starter Thread starter dhooie
  • Start date Start date
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
 
Hi

If the number of rows are not the same for each name then this is difficult to do
 
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...
 
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
 
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
 
Back
Top