Find and export matches in lists

[stephroberts11]

Hello. I have 2 sets of data, each with a list, whereby the 'emu list' is a source list of files I have searched for on servers. The 'results' list is the data about which files from the emu list have been located on servers, with associated data such as the file path. 

In Excel I would usually use a formula like =IF(ISERROR(VLOOKUP(A2,$B$1:$B$10, 1, FALSE)),FALSE,TRUE ) to determine which of the files in the emu list (col A) are in the results list (col B) i.e. which I have located.

I want to replicate this in Alteryx and have 2 output files - 1 with the TRUE matches (emu list files have been found) and one with the FALSE matches (emu list files were not found), with the output files to include all of the other columns i.e. B-C in the combined example below.

I have included some sample files: 'A- true match example combined' has the data combined onto one workbook. The 2 B files are the same data in separate files. 

I would very much appreciate your help on this. I have tried using Join but am still learning!

Thanks

Steph

[terry10 · Accepted solution]

@stephroberts11 

This can be accomplished with a Join, followed by a filter.

If my reply helped you, please mark the solution accepted.

[stephroberts11]

Thank you, this is very close to what I need! I have been comparing the results to those from Excel and while the TRUE outputs are correct, the FALSE are not. Ideally I would be able to export both from the same workflow but maybe this is not possible? The FALSE output should be:

Full emu list

1abc
2abc
abc5
abc7
100xyz
100xyz

I have updated the input data to include more rows containing some duplicate values in results list. If you are able to look into this some more that would be great but for now, thank you for providing the solution to getting my TRUE match data :)

[terry10]

@stephroberts11 

See the attached workflow for both True and False matches.

I wasn't sure what you intended to do with the duplicates. 🤷‍