vlookup or searching comma separated values in another comma separated values

[Pragnya]

Hi Team,

I have two excel files with tables.

First Book contains below data:

Items

A,B,C

D,E,F

G,H,I

Second Book contains below data :

Items	Values
X,Y,A,B,F,K	1
X,Y,A,B,C,F,K	2
L,M,N,D,E,X,Y	3
L,M,N,D,E,F,Z,F,K	4
X,Y,G,H,D,E	5
X,Y,G,H,I,L,M	6

I want to search book one items in the book two and if all items in the cell matches then get the respective value like v lookup.

Final output should looks like

Items	Values
A,B,C	2
D,E,F	4
G,H,I	6

Kindly help me in resolving the scenario.

[Jean-Balteryx]

Hi @Pragnya ,

Here is a workflow doing what you need !

It assigns a RecordID to each row then splits them to get one character per row. It joins to your reference to check if the value is present then group to count the number of values present for each recordID. It then joins to the total count of value for each row and retrieve the row if all values are present in the reference.

[Emil_Kos]

Hi @Pragnya,

I believe my solution is quite similar to what @Jean-Balteryx build.

The most important part is the text to columns. Splitting the data into rows make this task much easier to achieve.

Good luck on your Alteryx journey! 

[danilang]

hi @Pragnya 

If you know that items within each list will be in the same order, you can use a simple find replace to get the results

Dan

[HomesickSurfer]

Hi @Pragnya 

Here's my simple approach.

[Pragnya]

Hi Jean,

Thank you so much for your quick response & for your solution. Really great workaround.

[Pragnya]

Hi Homesicksurfer..

Thank you for your solution.. i think we can use your workflow only when the csv values are in sequential (adjacent to each other).

Please correct me if am wrong..

[Pragnya]

Thanks a lot each and every one who provided the workarounds. 

[HomesickSurfer]

Hi @Pragnya 

You are correct.