Faster way to check a list of unique IDs in one column

Question

Faster way to check a list of unique IDs in one column

John Walkers 20

I have an Excel file where column A contains over 1000 different IDs. I have already verified 476 of these IDs and saved them in a separate file. However, I forgot to mark them in the original file to distinguish which ones were verified. Is there a way to compare or look up all 476 IDs at once in the main file to quickly identify the verified ones, instead of checking them manually one by one? I’m using Office 365 with a company-provided account. The file was shared with me by someone else, and I’m not the owner of it.

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Ashish Mathur 100.8K Volunteer Moderator

Hi,

Here's one simple way. Copy the 476 ID's from the other file to the original file (in the same worksheet where the 1,000 ID's are). Select the 1,000 ID's, press Ctrl and select the 476 ID's and go to Home > Conditional formatting > Highlight duplicates.

Hope this helps.

Answer 2

Hi @John Walkers,

Good day to you! Welcome to Microsoft Q&A.

In order to faster check a list of unique IDs, please follow these steps:

Open both main and verified file.
In the main file, paste this formula: =IF(ISNA(XLOOKUP(A2, [verified_file.xlsx]Sheet1!$A$2:$A$477, [verified_file.xlsx]Sheet1!$A$2:$A$477, , 0)), "Not Verified", "Verified")

Eventually, this is the result of the formula:

User's image

If you have any further questions or concerns, please let us know. Looking forward to your response.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Open both workbooks.

Activate the main workbook.

Let's say the IDs begin in A2 on the sheet in the main workbook.

In row 2 in an empty column, enter the formula

=ISNUMBER(XMATCH(A2, '[Other Workbook.xlsx]Other Sheet'!$A$1:$A$2000))

where Other Workbook.xlsx is the name of the workbook with the verified IDs and Other Sheet is the name of the worksheet in that workbook.

Fill down from row 2 to the row of the last ID in column A. Rows with identified IDs will have TRUE, and rows with not yet identified IDs will have FALSE.

As you add new identified IDs to the second workbook, their rows in the main workbook will change from FALSE to TRUE.

Share via

Faster way to check a list of unique IDs in one column

2 additional answers

Your answer