Find unique values and then concatenate

[nikhil0311]

We have different countries available in column A

In Column B & C from row 3 we have the raw data

in cell B2 & C2 we have the final output

What I am looking for is

1 - In column B from B3:B12 find unique values

2 - concatenate the unique values with the countries present in column A (please refer output 1 & 2)

	Output 1	Output 2
Countries	Yes:AUNZ;MY;SG No:CN;IN;PH;TH	Complete : AUNZ CN IN MY 30 Day Activity : KR No: PH Not needed: TH
AUNZ	Yes	Complete
CN	No	Complete
IN	No	Complete
KR		30 Day Activity
MY	Yes	Complete
PH	No	No
SG	Yes
TW
TH	No	Not needed
VN

 

[cyrilbrd]

Hi,
What version of excel are you using?
If 365.

mrxcel2020.xlsx
	A	B	C	D
3	Countries	Output 1	Output 2
4	AUNZ	Yes	Complete
5	CN	No	Complete
6	IN	No	Complete
7	KR		30 Day Activity
8	MY	Yes	Complete
9	PH	No	No
10	SG	Yes
11	TW
12	TH	No	Not needed
13	VN
14
15	Output 1		Output 2
16	Yes	AUNZ,MY,SG	complete	AUNZ,CN,IN,MY
17	No	CN,IN,PH,TH	30 Day Activity	KR
18		KR,TW,VN	No	PH
19			Not needed	TH
20				SG,TW,VN
nikhil0311

Cell Formulas
Range	Formula
B16:B18	B16	=TEXTJOIN(",",TRUE,IF(Raw_data[Output 1]=A16,Raw_data[Countries],""))
D16:D20	D16	=TEXTJOIN(",",TRUE,IF(Raw_data[Output 2]=C16,Raw_data[Countries],""))

 

[cyrilbrd]

If by "generate unique values" you mean to extract from the raw data, try as follows:

mrxcel2020.xlsx
	A	B	C	D
3	Countries	Output 1	Output 2
4	AUNZ	Yes	Complete
5	CN	No	Complete
6	IN	No	Complete
7	KR		30 Day Activity
8	MY	Yes	Complete
9	PH	No	No
10	SG	Yes
11	TW
12	TH	No	Not needed
13	VN
14
15	Output 1	concatenated 1	Output 2	concatenated 2
16	0	KR,TW,VN	0	SG,TW,VN
17	No	CN,IN,PH,TH	Not needed	TH
18	Yes	AUNZ,MY,SG	No	PH
19			Complete	AUNZ,CN,IN,MY
20			30 Day Activity	KR
21
nikhil0311

Cell Formulas
Range	Formula
B16:B18	B16	=TEXTJOIN(",",TRUE,IF(Raw_data[Output 1]=A16,Raw_data[Countries],""))
A16:A19	A16	=IFERROR(LOOKUP(2,1/(COUNTIF($A$15:A15,Raw_data[Output 1])=0),Raw_data[Output 1]),"")
D16:D20	D16	=TEXTJOIN(",",TRUE,IF(Raw_data[Output 2]=C16,Raw_data[Countries],""))
C16:C21	C16	=IFERROR(LOOKUP(2,1/(COUNTIF($C$15:C15,Raw_data[Output 2])=0),Raw_data[Output 2]),"")

 

[Peter_SSs]

Firstly, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

FORMULAS
If you are looking for a formula approach & you have Excel 365 with the new dynamic array functions (UNIQUE & FILTER in this case) you could try this using helper cells.

Formula is entered in B15 only. Other unique values from column B will automatically 'spill' down to cells below.
Same for formula in D15
Formulas in C15 and E15 are copied down as far as you might ever need. (I have copied to row 20)
Formulas in B2 & C2 are stand-alone. These cells will need to have 'Wrap Text' set.

nikhil0311 2020-03-06 1.xlsm
	A	B	C	D	E
2		Yes:AUNZ;MY;SG No:CN;IN;PH;TH	Complete:AUNZ;CN;IN;MY 30 Day Activity:KR No:PH Not needed:TH
3	AUNZ	Yes	Complete
4	CN	No	Complete
5	IN	No	Complete
6	KR		30 Day Activity
7	MY	Yes	Complete
8	PH	No	No
9	SG	Yes
10	TW
11	TH	No	Not needed
12	VN
13
14
15		Yes	Yes:AUNZ;MY;SG	Complete	Complete:AUNZ;CN;IN;MY
16		No	No:CN;IN;PH;TH	30 Day Activity	30 Day Activity:KR
17				No	No:PH
18				Not needed	Not needed:TH
19
20
21
Formulas

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),1,C15:C20)
C2	C2	=TEXTJOIN(CHAR(10),1,E15:E20)
B15:B16	B15	=UNIQUE(FILTER(B3:B12,B3:B12<>""))
D15:D18	D15	=UNIQUE(FILTER(C3:C12,C3:C12<>""))
C15:C20	C15	=IF(B15="","",B15&":"&TEXTJOIN(";",1,FILTER(A$3:A$12,B$3:B$12=B15)))
E15:E20	E15	=IF(D15="","",D15&":"&TEXTJOIN(";",1,FILTER(A$3:A$12,C$3:C$12=D15)))
Dynamic array formulas.

VBA
If you are looking for a vba approach then you could try this user-defined function with no need for helper cells.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (use the icon at the top right of the code pane) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the B2 formula as shown in the screen shot below and copy across to C2. Again B2:C2 need 'Wrap Text'

Function Countries(rUnique As Range, rCountries As Range) As String
  Dim aU As Variant, aC As Variant, Ky As Variant
  Dim d As Object
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  aU = rUnique.Value
  aC = rCountries.Value
  For i = 1 To UBound(aU)
    If Len(aU(i, 1)) Then
      d(aU(i, 1)) = d(aU(i, 1)) & ";" & aC(i, 1)
    End If
  Next i
  For Each Ky In d.keys
    Countries = Countries & vbLf & Ky & ":" & Mid(d(Ky), 2)
  Next Ky
  Countries = Mid(Countries, 2)
End Function

nikhil0311 2020-03-06 1.xlsm
	A	B	C
2		Yes:AUNZ;MY;SG No:CN;IN;PH;TH	Complete:AUNZ;CN;IN;MY 30 Day Activity:KR No:PH Not needed:TH
3	AUNZ	Yes	Complete
4	CN	No	Complete
5	IN	No	Complete
6	KR		30 Day Activity
7	MY	Yes	Complete
8	PH	No	No
9	SG	Yes
10	TW
11	TH	No	Not needed
12	VN
13
vba

Cell Formulas
Range	Formula
B2:C2	B2	=Countries(B3:B12,$A3:$A12)

 

[nikhil0311]

i am using excel 2013

 

[Peter_SSs]

i am using excel 2013

Then I would suggest the vba approach.


I would also still suggest ..

.. that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.