Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I create multiple columns from a one-column list in Excel?
I have a one-column list with similar types of row data, that I would like to
use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
#2
|
|||
|
|||
I am going to assume that each block consists of the "header" plus 3 rows of
text, then a blank row (5 rows per block) and the data starts in A1. Put this formula in B1, and copy it to the right, through E1. Then copy B1:E1 down until the formulas begin to return 0's (i.e. you've run out of data in column A). =INDEX($A:$A,(ROW()-1)*5+COLUMN()-1) Then Edit/Copy all of the formulas, and, without changing the selection, Edit/Paste Special and select the Values option. Once you've done that, you can delete the original data in column A. If there's no blank row, change the 5 in the formula to 4, and just copy across to D1. If the number of rows in a block is not constant, you will probably need a VBA macro. On Tue, 4 Oct 2005 18:00:01 -0700, "Melissa" wrote: I have a one-column list with similar types of row data, that I would like to use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
#3
|
|||
|
|||
Assuming "Example A" is in cell A1
put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1 likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2 highlight B1 to E2 and drag copy down to get each row in the number increases by 5 that is down column B, you get a1, a6, a11 etc with this new block highlighted, substitute "a" with "=a" if you are happy with the result, delete column A "Melissa" wrote: I have a one-column list with similar types of row data, that I would like to use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
#4
|
|||
|
|||
Make sure you copy/paste special/value before deleting column A please
"PY & Associates" wrote: Assuming "Example A" is in cell A1 put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1 likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2 highlight B1 to E2 and drag copy down to get each row in the number increases by 5 that is down column B, you get a1, a6, a11 etc with this new block highlighted, substitute "a" with "=a" if you are happy with the result, delete column A "Melissa" wrote: I have a one-column list with similar types of row data, that I would like to use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
#5
|
|||
|
|||
Hi PY & Associates
Your reply has now given me a second way of sorting the data. I certainly appreciate your help. Cheers Melissa "PY & Associates" wrote: Make sure you copy/paste special/value before deleting column A please "PY & Associates" wrote: Assuming "Example A" is in cell A1 put "a1" in B1, "a2" in C1, "a3" in D1, "a4" in E1 likewise put "a6" in B2, "a7" in C2, "a8 in D2 "a9"in E2 highlight B1 to E2 and drag copy down to get each row in the number increases by 5 that is down column B, you get a1, a6, a11 etc with this new block highlighted, substitute "a" with "=a" if you are happy with the result, delete column A "Melissa" wrote: I have a one-column list with similar types of row data, that I would like to use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
#6
|
|||
|
|||
Hi Myrna
Thanks so much for your very prompt reply. Each block does have differing numbers of rows so I will need to do a VBA Macro. Thanks again Cheers Melissa "Myrna Larson" wrote: I am going to assume that each block consists of the "header" plus 3 rows of text, then a blank row (5 rows per block) and the data starts in A1. Put this formula in B1, and copy it to the right, through E1. Then copy B1:E1 down until the formulas begin to return 0's (i.e. you've run out of data in column A). =INDEX($A:$A,(ROW()-1)*5+COLUMN()-1) Then Edit/Copy all of the formulas, and, without changing the selection, Edit/Paste Special and select the Values option. Once you've done that, you can delete the original data in column A. If there's no blank row, change the 5 in the formula to 4, and just copy across to D1. If the number of rows in a block is not constant, you will probably need a VBA macro. On Tue, 4 Oct 2005 18:00:01 -0700, "Melissa" wrote: I have a one-column list with similar types of row data, that I would like to use to create a mulit-column spreadsheet. For example, the list currently looks like this: Example A text text text Example B text text text Every row is currently in column A. What I would like to do, without having to copy and paste (I have several hundred rows of text) is to have the data look like: Example A Text Text Text Example B Text Text Text I have looked at functions such as concatenate and Pivot tables but they don't seem to do what I need to do. I would appreciate any help with this. Melissa Queensland, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to merge data from multiple columns to one column | Excel Discussion (Misc queries) | |||
can i create multiple columns from the info in one cell | Excel Discussion (Misc queries) | |||
Is it possible to create one column with two different width? | Excel Worksheet Functions | |||
Is it possible to create one column with two different width? | Excel Worksheet Functions | |||
Pivot Table combining multiple columns | Excel Discussion (Misc queries) |