Forum Discussion
Cross Referencing
Hi All,
I have two huge lists of part codes on two separate files. one is a list of codes we have and one is a list of codes we need.
I want to cross reference these lists, taking out any codes that appear on both so just the part codes on the "need" list are codes that don't appear on the "got" list. I can then see what needs setting up.
Im sure there is a way of doing this but I am a novice and would very much appreciate some advice and guidance.
Thanks
Jamie
1 Reply
JamieofGilead When you talk about cross referencing "huge" lists, I would recommend that you look into using PowerQuery. Connect to the lists in both files (NOTE: you don't have to load the files into Excel) and merge the two with a so-called anti-join.
Set it up correctly once and you can re-use the query over and over again, should the lists change in the future.
Now, you mention to be a novice. So, PowerQuery might seem a bit overwhelming. But this would be a good starting point, though:
https://exceloffthegrid.com/power-query-introduction/ , in particular chapter 15.
Alternatively, you would have to get into VLOOKUP, INDEX/MATCH type of functions (or XLOOKUP if your Excel version supports it) and filters to find the items on the "need list" that are not on the "have list".
