Columns To Rows

[LX]

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

 

[Guest]

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:

 

[LX]

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

 

[Guest]

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:

 

[Guest]

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

regards

"Marcelo" escreveu:

 

[Herbert Seidenberg]

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.