I am trying to make an excel spreadsheet that easily allows to compare two similar lists and highlight non-matching values. Generally, the lists contain different categories, but one list can contain several replicates of one category, i.e., the lists are similar but do not follow the same layout.
Example list:
My current workflow looks like this: I filter out all data points for category A in a separate sheet, there I sort the data points to be in the right order and compare the values for each data point to get a true or false, if the values are the same or differ. I do this for all categories, on a separate sheet (using the same code/formulas).
As a final step, I would now like to go back to the original worksheet and highlight the differing data points and values for category A in list 1 and 2, then for B, then for C, etc. This is where I am stuck and don't know how to proceed. I am thinking of some conditional formatting formula that goes like "If category A, find "FALSE" in sheet2, take the data point next to false and highlight in original worksheet". And then make a new rule for category B, C and so on.
Does anyone have a good formula for this? I am also happy to take suggestions on how to improve my workflow. I am not too familiar with Macros, so I am open for ideas if a macro can make my life easier.
Appreciate any help!
