Is there a way to look for duplicate cells in the columns of two excel spreadsheets?

Question

I have two excel spreadsheets with ~10 digit numbers in a column on each sheet. I need to locate cells in column X of spreadsheet two that are duplicates of cells in column X of spreadsheet one so I can delete the line items. I don't need to compare the actual column as I don't care what line item each is on as the data was compiled via different methods. Beyond Compare seems to only let me look for exact line item duplicates, which doesn't help.

Example:

Spreadsheet one:
Bob Smith | 1112211 | other data
John Doe | 1122345 | other data
Jack Black | 56784 | other data

Spreadsheet two:
Bob Dylan | 1234554 | other data
Tenacious D | 56784 | other data
John Doe | 9999999 |

I need Jack Black/Tenacious D to match based on the matching number in the second column, even though they are on different lines. How can this be done? Note* I'm not a programmer - I can run a script, but not good at building them.

EDIT: It looks like some combination of IFERROR, MATCH, and COUNTIF gets me started, but I guess I'll just throw this out there in case there's a better way to do what I need than I can figure out. Here's my task:

I need to review workbook 2 to see if line items are duplicates of line items in workbook 1 based on info in column K in both workbooks. If it is a match, I need to review the line item to ensure it's not a typo, then ultimately the info in cell I of workbook 2 copied to cell I of workbook 1 if it doesn't already exist there, then the entire line of workbook 2 deleted.

My brain is hurting a bit. If I can figure this out it'll save me litteraly months of work. Suggestions?

Answer 1

A helper column on the first sheet with the following formula:

=IFERROR(INDEX(Sheet2!A:A,MATCH(Sheet1!B3,Sheet2!B:B,0)),"")

Should find all the duplicates:

Answer 2

i suggest you have a look at the excel countif function. see https://exceljet.net/excel-functions/excel-countif-function