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

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

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

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

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

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

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

How do I... 2 columns to 1 row
 

Or if your data is all text:
Excel 2007
http://www.mediafire.com/file/etz4dtmymfk/12_05_08.xlsx

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

How do I... 2 columns to 1 row
 

You say your data is in columns and you show rows???? Also, does col A
actually have numbers 1-4?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jesper Audi" wrote in message
...
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