Organising data, functions INDEX MATCH

[jdmc45]

Hi all,

I have data in the following form:
Column A – ID (have duplicate values)
Column B – Code (have duplicate values)
Column C – No. losses
Column D – No. gains
Column E – No. N/A’s
Column F – Total of column C, D, E
For Column A there are many duplicate values of the ID, for example
1234
1234
4567
7932
For Column B, there are also duplicate values. The codes are a subset of the ID, so for each ID I want the unique codes and then the values of column C to F for each.
I would like the data in the form of
A B
1234 EUT
BDT
4567 EUT
BDT
BGH
And then the values for columns C to F afterwards. Hope that explains it well enough!
Any help is appreciated cheers. Should I use some sort of INDEX, MATCH to get the unique values for each?

 

[jdmc45]

Sorry, maybe this will be easier to read

A                   B
1234              BDT
                    AUT
4567              BDT
                    AUT
                    EUT

 

[jdmc45]

It will look very similar to a pivot table with the unique portfolio ID listed once then the codes under it, like it would if you placed the ID and codes in the row labels part of a pivot table. Hope that helps!

 

[pagrender]

I"m not exactly sure what you are asking. Are you asking to fill in the blank cells with the data above it? If so, try this:

Highlight all your data
Hit F5 and Choose Special
Choose Blanks and hit OK (this will highlight all blank cells)
In the first cell that's highlighted, enter the cell reference you would like - then hit Control and Enter at the same time.

 

[jdmc45]

I just want to combine duplicate ID's and duplicate codes together, so it will read

unqiue ID #1 
         code #1
         code #2
unique ID #2 
         code#1
         code#2
         code#3

etc. The number of codes will change. I know a pivot table can do this exact job but I need it in spreadsheet format. Thanks.

 

[jdmc45]

anyone can help?