Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Need help converting 1 column of data to several columns

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

--
Message posted from http://www.ExcelForum.com

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default Need help converting 1 column of data to several columns

Hi

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"dhooie " wrote in message ...
First of all, thanks up front for any help.

I am a simple Excel user who desperately needs to convert a single
column of data that I imported from a text file that has several rows
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. Some
"groups" have extra rows of info, like serial # etc. What I'd like to
do is convert all the groups to a single row of data over multiple
columns and then shift all the new rows so that their respective
"fields" line up in the same column, but I can do that last part
manually.

What I really need to save me some time is a macro of some kind to get
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 to
run one.) so please be gentle in your explanations and use little words
with as few syllables as possible.

Thanks so much in advance!

David Hooie


---
Message posted from http://www.ExcelForum.com/



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,123
Default Need help converting 1 column of data to several columns

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)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"dhooie " wrote in message ...
First of all, thanks up front for any help.

I am a simple Excel user who desperately needs to convert a single
column of data that I imported from a text file that has several rows
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. Some
"groups" have extra rows of info, like serial # etc. What I'd like to
do is convert all the groups to a single row of data over multiple
columns and then shift all the new rows so that their respective
"fields" line up in the same column, but I can do that last part
manually.

What I really need to save me some time is a macro of some kind to get
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 to
run one.) so please be gentle in your explanations and use little words
with as few syllables as possible.

Thanks so much in advance!

David Hooie


---
Message posted from http://www.ExcelForum.com/





  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 171
Default Need help converting 1 column of data to several columns

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

Ron de Bruin wrote:
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



  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 102
Default Need help converting 1 column of data to several columns

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

"dhooie " wrote in message ...
First of all, thanks up front for any help.

I am a simple Excel user who desperately needs to convert a single
column of data that I imported from a text file that has several rows
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. Some
"groups" have extra rows of info, like serial # etc. What I'd like to
do is convert all the groups to a single row of data over multiple
columns and then shift all the new rows so that their respective
"fields" line up in the same column, but I can do that last part
manually.

What I really need to save me some time is a macro of some kind to get
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 to
run one.) so please be gentle in your explanations and use little words
with as few syllables as possible.

Thanks so much in advance!

David Hooie


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.593 / Virus Database: 376 - Release Date: 21/02/2004




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 171
Default Need help converting 1 column of data to several columns

Ron de Bruin and I each responded on February 26.

Alan Beban

Robert McCurdy wrote:
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting A String to a Column of Data carl Excel Worksheet Functions 2 May 19th 10 06:21 PM
Converting Address from one column to 8 columns Vijay DSK[_2_] Excel Discussion (Misc queries) 1 November 26th 08 07:54 AM
Converting data from every second to a new column PRC Excel Discussion (Misc queries) 4 August 21st 08 01:04 AM
Need help with Converting Rows of data into 2 columns Steve Excel Worksheet Functions 3 August 11th 05 04:41 AM
Converting CSV text in a single column, into multiple columns Kerry M. Soileau Excel Programming 1 October 4th 03 09:22 AM


All times are GMT +1. The time now is 10:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"