Return unique values in one column but with duplicate entries in another column for Excel

Detlef_Lewin Hi.

With Excel 365 robywoo's formula can be extended to spill the resulting table from one cell, L2.

The attached file has it built in three steps. 

The LET functions makes it possible to use helper columns in the same formula.

Changing the last parameter from the final output to eg IDs is a way to debug or analyse such formula.

=LET(IDs;UNIQUE(C:C);
counter;BYROW(IDs;LAMBDA(id;COUNT(UNIQUE(FILTER(A:A;C:C=id)))));
includingLabels;CHOOSE({1\2};counter;IDs);
output;INDEX(includingLabels;SEQUENCE(ROWS(includingLabels)-2;;2);SEQUENCE(;COLUMNS(includingLabels)));
output
)

 You could also add more functionality like changing row 5 to

SORT(output) or SORT(output,2) to sort by first or second column.

NB - my Excel uses national settings ; as delimiter between parameters instead of US ,

Easiest to use the attached file.