Forum Discussion
COMPARING COLUMNS
HELLO
CAN ANYONE TELL ME HOW TO COMPARE TWO COLUMNS OF DATA (ONE LONG, ONE SHORT) AND REVEAL WHAT IS MISSING FROM THE SHORT LIST PLEASE?
16 Replies
This formula detects any value that does not occur twice (or more) in the combined columns.
= UNIQUE(VSTACK(columnA, columnB),,TRUE)It appears that there are entries in the shorter list B that are not in A. One way of eliminating them from the result would be to modify the formula to read
= UNIQUE(VSTACK(columnA, columnB, columnB),,TRUE)
Let's say you have data in Sheet1!B2:B10000 and in Sheet2!F2:F500
Enter the word MISSING in C1 on Sheet1, and enter the following formula in C2 on Sheet1:
=ISERROR(XMATCH(B2, Sheet1!$F$2:$F$500))
Fill down from C2.
This will return TRUE for the values that are not in the short list, FALSE for the rest.
You can sort or filter on the new column.
- THANK YOU. IT IS VERY KIND OF YOU TO TRY AND HELP ME.
I HAVE FOLLOWED YOUR INSTRUCTIONS BUT WHEN i PULLED DOWN FROM C2 ALL THE CELLS READ false (EVEN THOUGH I KNOW THAT MOST ARE CORRECT)Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.
(Please turn off Caps Lock)
