Compare Two Delimited Lists and Determine if Values are the Same

[thestranger66]

Hello,

I am trying to come up with a function that compares two delimited lists to see if they are made up of the same values. Each list is in its own cell, can be variable length, and can have variable order. I'll give a few examples of inputs and outputs to display what I am trying to accomplish with my worksheet function below.

List 1	List 2	Output
A; B	A	F
A; B; 1	A; B	F
A	A; B	F
B; A	A; B	T
A; B	A; B	T

The best I've been able to come up with so far is to use a series of helper columns that returns individual values from each list so that they can be individually counted, but this isn't ideal since the lists can be around 8 items long and performance is an important factor for this solution. Thanks in advance to anyone who take the time to think through this.

-Steve

 

[Eric W]

Try:

Book1
	A	B	C
1	List 1	List 2	Output
2	A; B	A	FALSE
3	A; B; 1	A; B	FALSE
4	A	A; B	FALSE
5	B; A	A; B	TRUE
6	A; B	A; B	TRUE
7	Cat; Dog; Mouse	dog;mouse;cat	TRUE
8	Boat; car; motorcycle	moped; boat; car; motorcycle	FALSE
Sheet10

Cell Formulas
Range	Formula
C2:C8	C2	=AND(PRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(B2,";",REPT(" ",999)),999*{1,2,3,4,5,6,7,8}-998,999)),A2))),PRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(A2,";",REPT(" ",999)),999*{1,2,3,4,5,6,7,8}-998,999)),B2))))

 

[lrobbo314]

Here is a way using Power Query.

Book1
	A	B	C
1	List 1	List 2	Output
2	A;B	A	False
3	A;B;1	A;B	False
4	A	A;B	False
5	B;A	A;B	True
6	A;B	A;B	True
7	Cat;Dog;Mouse	Dog;Mouse;Cat	True
8	Boat;Car;Motorcycle	Moped;Boat;Car;Motorcycle	False
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ProperCase = Table.TransformColumns(Source,{{"List 2", Text.Proper, type text}, {"List 1", Text.Proper, type text}}),
    #"86Spaces" = Table.ReplaceValue(ProperCase," ","",Replacer.ReplaceText,{"List 1", "List 2"}),
    Output = Table.AddColumn(#"86Spaces", "Output", each List.Sort(Text.Split([List 1],";"))=List.Sort(Text.Split([List 2],";")))
in
    Output

 

[thestranger66]

Sorry for the delay in responding to this.

I ended up using Eric W.'s solution as I was looking for a worksheet formula and am not familiar with PowerQuery.

Thanks for the input!

 

[Eric W]

Glad we could help and thanks for the update!