Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
Hi,
I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
First column:
=INDEX(A$1:A$15,2*ROW(E1)-1) Second column: =INDEX(B$1:B$15,2*ROW(F1)-1) Third Column: =INDEX(B$1:B$15,2*ROW(G1)) Where the array references your original data, and the cell refernce at end is whatever cell you put the formula in. Basically, the formula looks up data, skipping every other cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Max" wrote: Hi, I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
Luke,
Thanks, but it is picking up data from every other cell, so the output is missing data. The data in column A can be many of the same number, so the output needs to have that many columns. ex: CH413 4194 CH413 4895 CH413 4195 CH413 9812 CH413 9817 CH413 9593 output: CH413 4194 4895 4195 etc Max "Luke M" wrote: First column: =INDEX(A$1:A$15,2*ROW(E1)-1) Second column: =INDEX(B$1:B$15,2*ROW(F1)-1) Third Column: =INDEX(B$1:B$15,2*ROW(G1)) Where the array references your original data, and the cell refernce at end is whatever cell you put the formula in. Basically, the formula looks up data, skipping every other cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Max" wrote: Hi, I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
Hi,
Lets assume your data is in columns A and B starting in A2. Then in C2 enter the following formula and copy it down and to the right as far as necessary: =IF($A2=$A1,"",IF($A2=OFFSET($A2,COLUMN(A2),0),OFF SET($B2,COLUMN(A2),0),"")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Max" wrote: Luke, Thanks, but it is picking up data from every other cell, so the output is missing data. The data in column A can be many of the same number, so the output needs to have that many columns. ex: CH413 4194 CH413 4895 CH413 4195 CH413 9812 CH413 9817 CH413 9593 output: CH413 4194 4895 4195 etc Max "Luke M" wrote: First column: =INDEX(A$1:A$15,2*ROW(E1)-1) Second column: =INDEX(B$1:B$15,2*ROW(F1)-1) Third Column: =INDEX(B$1:B$15,2*ROW(G1)) Where the array references your original data, and the cell refernce at end is whatever cell you put the formula in. Basically, the formula looks up data, skipping every other cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Max" wrote: Hi, I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
That did it!
Regards Max "Shane Devenshire" wrote: Hi, Lets assume your data is in columns A and B starting in A2. Then in C2 enter the following formula and copy it down and to the right as far as necessary: =IF($A2=$A1,"",IF($A2=OFFSET($A2,COLUMN(A2),0),OFF SET($B2,COLUMN(A2),0),"")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Max" wrote: Luke, Thanks, but it is picking up data from every other cell, so the output is missing data. The data in column A can be many of the same number, so the output needs to have that many columns. ex: CH413 4194 CH413 4895 CH413 4195 CH413 9812 CH413 9817 CH413 9593 output: CH413 4194 4895 4195 etc Max "Luke M" wrote: First column: =INDEX(A$1:A$15,2*ROW(E1)-1) Second column: =INDEX(B$1:B$15,2*ROW(F1)-1) Third Column: =INDEX(B$1:B$15,2*ROW(G1)) Where the array references your original data, and the cell refernce at end is whatever cell you put the formula in. Basically, the formula looks up data, skipping every other cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Max" wrote: Hi, I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
Excel 2007
Pivot Table adjusts automatically with data size and repetitions. http://www.mediafire.com/file/zdnwoyjmznz/12_05_08.xlsx |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
Hey Shane i have also this type of data
for example I have 3 Column Like this Doug 15 8-Mar-91 Doug 24 9-Apr-95 Doug 30 16-Dec-96 Doug 25 17-Jul-98 Pai 21 30-Dec-77 Pai 15 21-Apr-82 Pai 20 7-Sep-03 Pai 25 24-Apr-05 Pai 24 10-Jul-79 Pai 30 13-Jan-81 Pai 40 29-Oct-85 I want like this Doug 15 8-Mar-91 24 9-Apr-95 30 16-Dec-96 25 17-Jul-98 Pai 21 30-Dec-77 15 21-Apr-82 20 7-Sep-03 25 24-Apr-05 30 13-Jan-81 40 29-Oct-85 Any idea Thanks in Advance Hardeep kanwar "Shane Devenshire" wrote: Hi, Lets assume your data is in columns A and B starting in A2. Then in C2 enter the following formula and copy it down and to the right as far as necessary: =IF($A2=$A1,"",IF($A2=OFFSET($A2,COLUMN(A2),0),OFF SET($B2,COLUMN(A2),0),"")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Max" wrote: Luke, Thanks, but it is picking up data from every other cell, so the output is missing data. The data in column A can be many of the same number, so the output needs to have that many columns. ex: CH413 4194 CH413 4895 CH413 4195 CH413 9812 CH413 9817 CH413 9593 output: CH413 4194 4895 4195 etc Max "Luke M" wrote: First column: =INDEX(A$1:A$15,2*ROW(E1)-1) Second column: =INDEX(B$1:B$15,2*ROW(F1)-1) Third Column: =INDEX(B$1:B$15,2*ROW(G1)) Where the array references your original data, and the cell refernce at end is whatever cell you put the formula in. Basically, the formula looks up data, skipping every other cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Max" wrote: Hi, I have 2 columns of data that look like this CH301 9087 CH301 9021 CH412 9594 CH412 4897 I need them to look like this CH301 9087 9021 CH412 9594 4897 The number in column "A" can from single to multiple There are 1,628 rows incl header Could anybody suggest a good way to do this? Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I... 2 columns to 1 row
In your second example relating to text, I wonder if you would be able to add
column number, as you did for values and then get the correct text in the correct column. In my example, I have business cards which mostly have a random number of fields So business card1 might have Col A Col B Col C 1 Name Joe 2 Add1 Bay St 3 Tel 4421 4 Email Joe@XX Business card2 might have only 2 fields Col A Col B Col C 1 Name Dave 4 Email Dave@XX But I want to transpose my data so that name is always in col1, add1 is always in col2, tel is always in col3, email is in col4 Actually it is more complex than that as I have 5,000 business cards each with a random number of fields up to 20 and I need a layout that has one row for each name, with the text/ numbers stretching out across the 20 columns in correct field order, with gaps where there is no field Help would be appreciated Jesper |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
putting 2 long columns into multiple columns in excel page and sor | Excel Discussion (Misc queries) | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) |