Reporting unique values from a range

[adi2809]

Hi,

I need help to prepare a report with the following format:-
Input
ID Name
1 A
2 B
3 B
4 C
5 C
6 A
7 D
8 C

Output
Name    ID
A          1
            6
B          2
            3
C          4
            5
            8
D          7

I am able to get the unique list using the array formula:-

=INDEX($B$3:$B$10, MATCH(0, COUNTIF($G$2:G2, $B$3:$B$10), 0))

But need help to create the report as per above format.Looking for a solution that can handle any addition or deletion of Name

 

[Luke M]

Not a formula solution, but if you create a PivotTable, you can easily create that output.

 

[SirJB7]

Hi, adi2809!

If I were you I'd follow Luke M's advise, but if you insist on a formula only solution check the uploaded file.

It uses a helper column C for counting the occurrences of each Name, it builds a unique names list in column E, counts how many of them in column F, and in column G it accumulates F values, starting by zero at G1.

C2: =CONTAR.SI(B$2:B2;B2) -----> in english: =COUNTIF(B$2:B2,B2)
E2: =SI.ERROR(INDICE(B$2:B$9;COINCIDIR(0;CONTAR.SI(E$1:E1;B$2:B$9);0));"") -----> in english: =IFERROR(INDEX(B$2:B$9,MATCH(0,COUNTIF(E$1:E1,B$2:B$9),0)),"")
F2: =CONTAR.SI(B$2:B$9;E2) -----> in english: =COUNTIF(B$2:B$9,E2)
G2: =SUMA(F$2:F2) -----> in english: =SUM(F$2:F2)

The output:
I1: =B1
J1: =A1
I2: =INDICE(E$2:E$9;COINCIDIR(FILA()-1-0,1;G$1:G$9;1)) -----> in english: =INDEX(E$2:E$9,MATCH(ROW()-1-0.1,G$1:G$9,1))
J2: =INDICE(A$2:A$9;COINCIDIR(I2&"_"&CONTAR.SI(I$2:I2;I2);B$2:B$9&"_"&C$2:C$9;0)) -----> in english: =INDEX(A$2:A$9,MATCH(I2&"_"&COUNTIF(I$2:I2,I2),B$2:B$9&"_"&C$2:C$9,0))

Remember that formulas at columns E and J are array formulas and should be entered with Ctrl-Shift-Enter instead of just Enter.

Copy down as required.

Just advise if any issue.

Regards!