Eliminate duplicates in worksheets

I have two worksheets, 1st 'Rooster' and 2nd 'Paid' and I want to create a third worksheet 'Unpaid'. The first worksheet has a list of the complete rooster of names. The second worksheet has a list of who Paid. I need to compare the two worksheets and on the third worksheet list only the names who have not paid. I also need to pull the data from the rest of the columns from the first worksheet(Rooster) into the third (Unpaid).


Rooster worksheet
Name Address City Zip code Email
Mary 123 Elm St. Louis 65431 [email protected]
Jane 456 Pine New York 32145 [email protected]
Bill 879 Oak los angeles 98888 [email protected]
Sam 987 Redwood austin 65432 [email protected]
Joe 576 Dogwood salt lake city 55555 [email protected]
Sue 762 Main tempi 46263 [email protected]
John 987 Jasper dallas 87654 [email protected]


Paid worksheet
Name Address City Zip code Email
Mary 123 Elm St. Louis 65431 [email protected]
Bill 879 Oak los angeles 98888 [email protected]
Joe 576 Dogwood salt lake city 55555 [email protected]
John 987 Jasper dallas 87654 [email protected]



What I would need
Upaid worksheet
Name Address City Zip code Email
Jane 456 Pine New York 32145 [email protected]
Sam 987 Redwood austin 65432 [email protected]
Sue 762 Main tempi 46263 [email protected]


Thank you for your help

They came from two different excel spreadsheets and I'm trying to combine them and compare. This was done manual to see who has not paid.

Why do this?

Can't you just have a single sheet with a column for Paid/Unpaid, then filter for Paid or Unpaid as required?

This would be much easier to maintain and far less error prone.

As you already have two worksheets, one with all the Data (Sheet1), and one with Customers Paid (Sheet2)

1/. Concatenate enough cells to get an unique string, say Name, Address, and City.
In Sheet2 G2

Please Login or Register  to view this content.

Drag/Fill Down to last paid record.

2/. Concatenate the matching columns on Sheet1
In Sheet1 I2

Please Login or Register  to view this content.

Drag/Fill Down to last record.

We now have two unique columns to compare
In Sheet1 H2

Please Login or Register  to view this content.

Drag/Fill Down to last record.

This will return "Paid" if a match is found in Sheet2

3/. Copy column H and Paste Special > Values to Column F
This removes the formula and returns the value only.

Column H and I on Sheet1 can now be deleted, as can Column G on Sheet2.

4/. In column F Sheet1 we can now add Data Validation
Data Validation > Allow:= List Source:= Paid, Not Paid (no quotes)

5/. Apply Filter to Columns A:F then select the conditions you want to see from the drop-down in F1

6/. Finally we can apply Conditional Formatting if you wish

Condition 1
Formula:=

Please Login or Register  to view this content.

Fill:= Red
Applies to:=

Please Login or Register  to view this content.

(change $22 to as many rows as you need to e.g. $1000)

Condition 2
Formula:=

Please Login or Register  to view this content.

Fill:= Yellow
Applies to:=

Please Login or Register  to view this content.

(change $22 to match Condition 2)

This set-up will colour rows that are flagged "Not Paid" Red, unflagged Yellow, and "Paid" no fill.
Select from the drop-downs or delete cell value in Column F to see the result.


This gives you a single sheet to work with and maintain or add to as required.

See the attached workbook for a demo of these steps.

Hope this helps.