Unique Records or Transpose Help

  • Thread starter Thread starter JKurt74
  • Start date Start date
J

JKurt74

I have data in two columns that looks like Ref1 below. Column A has a value
repeated for however many times a value in column B shows up. I need unique
records in column A so that it displays like Ref2 below but I don't care how
many columns that converts to. Is there a way to do that without using VB?
I can paste special transpose for a few records, but I have thousands?

Ref1
ColA ColB
1 A
2 A
2 B
2 C
3 A
4 A
4 B
6 D
6 F
6 J

Ref2
ColA ColB ColC ColD
1 A
2 A B C
3 A
4 A B
6 D F J
Thanks in advance for the help.
 
I have data in two columns that looks like Ref1 below. Column A has a value
repeated for however many times a value in column B shows up. I need unique
records in column A so that it displays like Ref2 below but I don't care how
many columns that converts to. Is there a way to do that without using VB?
I can paste special transpose for a few records, but I have thousands?

Ref1
ColA ColB
1 A
2 A
2 B
2 C
3 A
4 A
4 B
6 D
6 F
6 J

Ref2
ColA ColB ColC ColD
1 A
2 A B C
3 A
4 A B
6 D F J
Thanks in advance for the help.
Click to expand...

Assuming your data (Ref1) is in Sheet1, you may try this in Sheet2:

In cell A1 put
=Sheet1!A1

In cell A2 put
=INDEX(Sheet1!A$1:A$100,MATCH(TRUE,INDEX(ISNA(MATCH(Sheet1!A$1:A$100,A$1:A1,0)),0),0))

Copy cell A2 down as far as needed

In cell B1 put
=IF(COUNTIF(Sheet1!$A$1:$A$100,$A1)>COLUMN()-2,INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100=$A1,ROW(Sheet1!$A$1:$A$100)),COLUMN()-1)),"")

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 on all places to fit the size of your data on Sheet1
(Ref1)

Copy cell B1 to the right as far as needed, e.g. to cell J1
Copy cells B1 to J1 down as far as needed.

Hope this helps / Lars-Åke
 
Back
Top