Formula To Compare Lists on Multiple Sheets and Return the Rows (or just the Values)

New Contributor



I will try to explain this the best that I can. I have 3 categories (let's say A, B, and C) with about 5 Items (1, 2, 3, 4, and 5) in each category. My goal is to have a formula to see which duplicates may exist between the sheets. To be more specific, I have an individual sheet for each Item, and have been sent down a Master List to go through. However, the Master list is going to contain a lot of information that I have already done on the Individual lists. I was hoping to have a formula that I can implement to tell me what I have already gone through to save time going through them all again so that I can save the cells that I have already worked on with the new list given to me, and then focus on working on the ones that I have not yet worked on. I hope this makes sense. Each Item list has somewhere between 20 and 100 cells to go through and each Master List contains about 200 cells.


Thank you!

1 Reply

@CodyThomas For anyone in a similar situation, I ended up using Power Query to compare the lists and return a new list that did not include duplicates from the list it was being compared to. The only issue was that to compare multiple lists, I had to then Merge my other lists individually with the previously merged list, creating a lot of merges. This also takes out any formatting for some reason (I did not see an option anywhere to retain formatting), therefor I still have some work to do to make sure it is formatted the same to avoid future confusion when sending back my updated list to management. I am still interested in a formula that could manage this trick, as I would definitely have other uses for it as well. Aside from that, if anyone has any tips for transferring cell formatting to different tables, I am interested in quick tips to save some time there too. Thank you, and if you are curious what process I used for the Power Query, here is the link: .