Hi,

I have some data which I need some help with converting from columns to
rows. The data is in the format:

Col A Col B
A 1
A 2
A 3
B 4
C 5
C 6

The output will look like:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

Basically, the initial sheet will contain Column A which will be the
identifier and Column B which has unique values for each identifier.
The output I am hoping to get is for each unique identifier such as
"A", get the total number of values (1,2,3) that correspond and place
them into seperate columns as shown above.

Is there a way to achieve the above without writing a macro ??

Thanks

Hi LX,

If you do not talking about tons of rows or different identifier, an easy
way is copy | special past | values | transpose - per group of identifier

as

A 1
A 2
A 3

will

A A A
1 2 3

regards

"LX" escreveu:

Hi,

I have some data which I need some help with converting from columns to
rows. The data is in the format:

Col A Col B
A 1
A 2
A 3
B 4
C 5
C 6

The output will look like:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

Basically, the initial sheet will contain Column A which will be the
identifier and Column B which has unique values for each identifier.
The output I am hoping to get is for each unique identifier such as
"A", get the total number of values (1,2,3) that correspond and place
them into seperate columns as shown above.

Is there a way to achieve the above without writing a macro ??

Thanks

Hi Marcelo,

Thanks for the help but I do need it in the format:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

This was a simple example but the worksheet I am dealing with has
thousands of rows and many identifiers.

Thanks

Marcelo wrote:
Hi LX,

If you do not talking about tons of rows or different identifier, an easy
way is copy | special past | values | transpose - per group of identifier

as

A 1
A 2
A 3

will

A A A
1 2 3

regards

"LX" escreveu:

Hi,

I have some data which I need some help with converting from columns to
rows. The data is in the format:

Col A Col B
A 1
A 2
A 3
B 4
C 5
C 6

The output will look like:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

Basically, the initial sheet will contain Column A which will be the
identifier and Column B which has unique values for each identifier.
The output I am hoping to get is for each unique identifier such as
"A", get the total number of values (1,2,3) that correspond and place
them into seperate columns as shown above.

Is there a way to achieve the above without writing a macro ??

Thanks

Sure, create a new colomn left and cut and past the col A like:


Col A Col B Col C Col D
A A A
1 2 3

So,

Col A Col B Col C Col D Col E
A A
A 1 2 3

exclude the first line

hope its help
Marcelo




"LX" escreveu:

Hi Marcelo,

Thanks for the help but I do need it in the format:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

This was a simple example but the worksheet I am dealing with has
thousands of rows and many identifiers.

Thanks

Marcelo wrote:
Hi LX,

If you do not talking about tons of rows or different identifier, an easy
way is copy | special past | values | transpose - per group of identifier

as

A 1
A 2
A 3

will

A A A
1 2 3

regards

"LX" escreveu:

Hi,

I have some data which I need some help with converting from columns to
rows. The data is in the format:

Col A Col B
A 1
A 2
A 3
B 4
C 5
C 6

The output will look like:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

Basically, the initial sheet will contain Column A which will be the
identifier and Column B which has unique values for each identifier.
The output I am hoping to get is for each unique identifier such as
"A", get the total number of values (1,2,3) that correspond and place
them into seperate columns as shown above.

Is there a way to achieve the above without writing a macro ??

Thanks

Sorry, if you have a thousands of rows this way will not help you..

regards

"Marcelo" escreveu:

Sure, create a new colomn left and cut and past the col A like:


Col A Col B Col C Col D
A A A
1 2 3

So,

Col A Col B Col C Col D Col E
A A
A 1 2 3

exclude the first line

hope its help
Marcelo




"LX" escreveu:

Hi Marcelo,

Thanks for the help but I do need it in the format:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

This was a simple example but the worksheet I am dealing with has
thousands of rows and many identifiers.

Thanks

Marcelo wrote:
Hi LX,

If you do not talking about tons of rows or different identifier, an easy
way is copy | special past | values | transpose - per group of identifier

as

A 1
A 2
A 3

will

A A A
1 2 3

regards

"LX" escreveu:

Hi,

I have some data which I need some help with converting from columns to
rows. The data is in the format:

Col A Col B
A 1
A 2
A 3
B 4
C 5
C 6

The output will look like:

Col A Col B Col C Col D
A 1 2 3
B 4
C 5 6

Basically, the initial sheet will contain Column A which will be the
identifier and Column B which has unique values for each identifier.
The output I am hoping to get is for each unique identifier such as
"A", get the total number of values (1,2,3) that correspond and place
them into seperate columns as shown above.

Is there a way to achieve the above without writing a macro ??

Thanks

Assume your data looks like this,
changed to cover more contingencies:
BinA BinB
A 11
B 12
C 13
A 14
C 15

A 16

BinC 1 2 3 Coln
A 11 14 16
B 12
C 13 15

Name BinA and BinB.
Create BinC with Advanced Filter, unique records, and name it.
Create the horizontal header Coln and name it Coln.
It is a sequence of numbers from 1 to n
n=MAX(COUNTIF(BinA,BinA)) This is an array formula.
Tools Options General R1C1 Ref Style
At the intersection of BinC=A and Coln=1 enter this array formula
=IF(COUNTIF(BinA,BinC R)=Coln C,
SMALL((BinA=BinC R)*BinB,ROWS(BinA)-
COUNTIF(BinA,BinC R)+Coln C),"")
Fill the rest of the array with the fill handle.
Uncheck R1C1 Ref Style.