Unique value in column A with transposed values from column B

Vlada

New Member
Joined
Jun 30, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi dear Experts,

In which way this can be done?
1715763204800.png


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!!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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)))))))
 
Upvote 1
Solution
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 !!
 
Upvote 0
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,"")))
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top