Unique records

[FLHR01]

Hi,

I'm looking for the most efficient way to find duplicate records and delete BOTH occurrences of the records.

We typically sort the left table and then match it the right table (yellow) and delete row by row.

Takes about half hour to complete.

There MUST be an easier way.

Book1.xlsx

[ChemistB]

So the record needs to be the same for all 4 columns (J,K,L,M) Can we not worry about discrepancies in Pay and Hours and focus on Date and Employee number?

[FLHR01]

Yes, records need to be identical in all 4 columns.

[JohnTopley]

What fields constitute a duplicate?

[FLHR01]

A,C,E,G need to match J, K, L, M

[ChemistB]

Here's how I would do it.
I am going to assume in real life, the two tables are not next to each other. Is this true?

To the right of each table, concatenate Emp Number and WorkDate
H2=A2&G2
O2=K2&N2

Then in the next column i.e. in I2 copied down
=ISNUMBEr(MATCH(H2, O:O,0))
Do the same for the second table

You could now filter on TRUE and delete those rows. HOWEVER, that would change the TRUE/FALSE values immediately on the second table. So what we need to do first is make those TRUE/FALSES permanent
Copy (col I for example) , Paste Special > Values. Do the same for Column P. Now you can filter and erase rows.

[FLHR01]

the yellow table IS copy and pasted from the previous pay period next the current one. my attahment is exactly what it looks like right before I sort the left table.

[cyiangou]

I hope I understood your requirement: If any records match BETWEEN tables, the matching records should be deleted from both tables?

If so, then this solution identifies the records that should be deleted.

unique_records.xlsx

[ChemistB]

You can follow my process just
H2= A2&C2&E2&G2
O2= K2&L2&M2&N2

And when you delete, make sure you don't delete the whole row (i.e. other table)

[FLHR01]

Thanks all. I will test it out this afternoon.