Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting A String to a Column of Data | Excel Worksheet Functions | |||
Converting Address from one column to 8 columns | Excel Discussion (Misc queries) | |||
Converting data from every second to a new column | Excel Discussion (Misc queries) | |||
Need help with Converting Rows of data into 2 columns | Excel Worksheet Functions | |||
Converting CSV text in a single column, into multiple columns | Excel Programming |