[Solved] Search for duplicates in two Calc files

Discuss the spreadsheet application
Locked
THEBookMan
Posts: 117
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

[Solved] Search for duplicates in two Calc files

Post by THEBookMan »

Can I search for duplicates in 2 separate CALC files ?

I have 1 CALC file (120,000 E-Mails) and another (1,000 E-Mails).

I would like to search for each E-Mail in the smaller file to see if there is a duplicate in the larger file.

"FIND AND REPLACE" works well, but takes a *L*O*T* of time, csince I have to do each one separetly.

Thanks, as always,

⌡im [THE BookMan]
Last edited by MrProgrammer on Thu May 29, 2025 4:04 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Open Office 4.1.3 Win 10
THEBookMan
Posts: 117
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Search for duplicates in 2 separate Calc files

Post by THEBookMan »

Better / Simpler solution:

Do it all in 1 spreadsheet
Column A - list of 120,000 E-Mails
Column B - date I sent them out
Column D - list of E-Mails I want to be sure are not duplicate\d in Column A
Column E - Formula =if(D1={any cell in column A};"►";".")
Possibly use "RANGE" ?

Again, as always, Thank you,
⌡im [THE BookMan]
Open Office 4.1.3 Win 10
Alex1
Volunteer
Posts: 830
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Search for duplicates in 2 separate Calc files

Post by Alex1 »

Use the MATCH function.
AOO 4.1.15 & LO 24.8.4 on Windows 10
THEBookMan
Posts: 117
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Search for duplicates in 2 separate Calc files

Post by THEBookMan »

Please provide an example.
I have tried several ... to no avail

TNX
Open Office 4.1.3 Win 10
Alex1
Volunteer
Posts: 830
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: Search for duplicates in 2 separate Calc files

Post by Alex1 »

Move column D to another sheet, insert a row before row 1, enter =MATCH(D2;Sheet1.A$1:A$12000;0) in Sheet2.E2 and doubleclick the fill handle. Enter header names in row 1. Turn on the autofilter in row 1 and select the rows with #N/A in column E. Then copy column D to another sheet.
AOO 4.1.15 & LO 24.8.4 on Windows 10
Locked