Unique value in column A with transposed values from column B

[Vlada]

Hi dear Experts,

In which way this can be done?

So to get unique values in column A, while values from B to be transposed in one cell, separated by ; .

I tried with array formula, but is braking due to huge number of data, therefore maybe VBA code could me more suitable?

Any help is much appreciated!!

 

[hagia_sofia]

If we suppose that your headers are in A1 and B1 respectively, then try this formula (adjust the ranges based on actual data):

=LET(countries,UNIQUE(A2:A12),HSTACK(countries,BYROW(countries,LAMBDA(a,TEXTJOIN(";",,TRANSPOSE(FILTER(B2:B12,A2:A12=a)))))))

 

[Vlada]

If we suppose that your headers are in A1 and B1 respectively, then try this formula (adjust the ranges based on actual data):

=LET(countries,UNIQUE(A2:A12),HSTACK(countries,BYROW(countries,LAMBDA(a,TEXTJOIN(";",,TRANSPOSE(FILTER(B2:B12,A2:A12=a)))))))

Works perfectly! Truly magnificent formula, thank you so much !!

 

[JV0710]

Hi, I have Office 365 but a version that does NOT yet have "Hstack" and "Lamda" etc . . . so I had to find another way for a similar problem
The below formula worked for me -

Assuming your headers are in A1 and B1 respectively and your results are in columns D & E
The country Name in D2 and the formula in E2
formula in E2 : =TEXTJOIN(",",TRUE,UNIQUE(FILTER($B$2:$B$695,$A$2:$A$695=D2,"")))

 

[alansidman]

Apply the concept shown in this tutorial employing Power Pivot

https://www.sfmagazine.com/articles/2018/july/excel-reporting-text-in-a-pivot-table/