I have three columns of numerical values in Excel:
Column A contains 2444 numerical values
Column B contains 2838 numerical values. I have entered the following formula in cell C2 to remove leading zeroes of the numerical values and output the result to Column C: =VALUE(TRIM(CLEAN(B2:B2839)))
Column C contains 2838 numerical values with leading zeroes removed
I want to compare the 2444 numerical values in Column A against the 2838 numerical values in Column C to identify the numerical values that are contained in Column A but not contained in Column C
I have explored conditional formatting rules on Excel but haven't been able to determine which would be appropriate.
Any insight would be greatly appreciated! Thank you
CodePudding user response：
Put a column next to A that has all X's. Then just do a vlookup using the values in B and bring in the X's. If a value doesn't have an X, it's not on list A. Or vice versa if that's applicable.
CodePudding user response：
Assuming you have spill range, if you put this formula in a cell that can spill down, you'll get a distinct list of every cell in column A that does not exist in column C
=UNIQUE(FILTER(A:A,NOT(ISNUMBER(MATCH(A:A,C:C,0)))*(A:A<>"")))