Extracting Unique Values

[cheongmarcus]

Hi everyone, I'm glad that this platform has helped to resolve my queries previously regarding excel. I need help on extracting unique values from 2 separate data set. It has more rows than these but i just extracted a sample to pose my question. Originally, Data set #1 is in worksheet 1 while data set #2 is in worksheet 2. To show it clearer, i have put in the same worksheet for now.

My question is: I want to extract unique values from these 2 data sets based on the month (if you see cell I2 to K2) using a formula. So each month will list out all the unique countries from both the list of countries combined.
Can anyone help me with this? Any help is much appreciated. Thank you.

 

[shaowu459]

Hi, I'd like to provide a solution from a different perspective. It is easy to get a list of all country names of the world, store this list in one column, then user formula to check whether the country name and month exist in the datasource, if yes, then show it.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Data1		Data2		Country Name		Jan	Feb	Mar
2	A	Jan	A	Feb	A		A	A	C
3	B	Jan	C	Jan	B		B	G	E
4	C	Mar	Z	Feb	C		C	V	M
5	D	Jan	V	Feb	D		D	Z
6	E	Mar	B	Jan	E		F
7	F	Jan	M	Mar	F
8	G	Feb	D	Jan	G
9					H
10					I
11					J
12					K
13					L
14					M
15					N
16					O
17					P
18					Q
19					R
20					S
21					T
22					U
23					V
24					W
25					X
26					Y
27					Z
Sheet1

Cell Formulas
Range	Formula
G2:G6,I2:I4,H2:H5	G2	=FILTER($E$2:$E$27,COUNTIFS($A$1:$A$1000,$E$2:$E$27,$B$1:$B$1000,G$1)+COUNTIFS($C$1:$C$1000,$E$2:$E$27,$D$1:$D$1000,G$1))
Dynamic array formulas.

 

[cheongmarcus]

I see, thank you @shaowu459. May I know is it possible to not join both the list into one column but instead reference the lists separately?

because both the lists are actually in separate tabs and not within the same worksheet. And say if I have multiple tabs (e.g. 10 data sets instead of 2) I would prefer if i do not need to create an additional column to join all.

Thanks.

 

[Peter_SSs]

Perhaps theis user-defined function might help. 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 (can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down. (If you have more than 2 ranges then list them as further arguments in the function, separated by commas)
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Country(sMonth As String, Num As Long, ParamArray Ranges() As Variant) As String
  Dim d As Object
  Dim a As Variant, Countries As Variant
  Dim i As Long, rng As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  For rng = LBound(Ranges) To UBound(Ranges)
    a = Ranges(rng).Value
    For i = 1 To UBound(a)
      If InStr(1, d(a(i, 2)) & " ", " " & a(i, 1) & " ", 1) = 0 Then d(a(i, 2)) = d(a(i, 2)) & " " & a(i, 1)
    Next i
  Next rng
  Countries = Split(d(sMonth))
  If Num <= UBound(Countries) Then Country = Countries(Num)
End Function

cheongmarcus 2020-05-22 1.xlsm
	A	B
1	Data2
2	A	Apr
3	C	Jan
4	A	Apr
5	V	Apr
6	B	Jan
7	M	Mar
8	D	Jan
9	X	Mar
10
Sheet2

cheongmarcus 2020-05-22 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Data1								Jan	Feb	Mar	Apr
2	A	Jan							A		C	A
3	B	Jan							B		E	V
4	C	Mar							C		M
5	A	Jan							D		X
6	E	Mar
7	B	Jan
8	A	Apr
9
Sheet1

Cell Formulas
Range	Formula
I2:L7	I2	=Country(I$1,ROWS(I$2:I2),$A$2:$B$9,Sheet2!$A$2:$B$9)

 

[shaowu459]

I see, thank you @shaowu459. May I know is it possible to not join both the list into one column but instead reference the lists separately?

because both the lists are actually in separate tabs and not within the same worksheet. And say if I have multiple tabs (e.g. 10 data sets instead of 2) I would prefer if i do not need to create an additional column to join all.

Thanks.

it is not easy to do that in formula, VBA method in post #4 is a better way for your case.

 

[cheongmarcus]

Thank you so much for the help @Peter_SSs, I will take a look at your method and try it now. Thanks too @shaowu459.