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 st.louis@yahoo.com
Jane 456 Pine New York 32145 newyork@gmail.com
Bill 879 Oak los angeles 98888 losangeles@hotmail.com
Sam 987 Redwood austin 65432 austin@msn.com
Joe 576 Dogwood salt lake city 55555 saltlakecity@yahoo.com
Sue 762 Main tempi 46263 tempi@gmail.com
John 987 Jasper dallas 87654 dallas@hotmail.com


Paid worksheet
Name Address City Zip code Email
Mary 123 Elm St. Louis 65431 st.louis@yahoo.com
Bill 879 Oak los angeles 98888 losangeles@hotmail.com
Joe 576 Dogwood salt lake city 55555 saltlakecity@yahoo.com
John 987 Jasper dallas 87654 dallas@hotmail.com



What I would need
Upaid worksheet
Name Address City Zip code Email
Jane 456 Pine New York 32145 newyork@gmail.com
Sam 987 Redwood austin 65432 austin@msn.com
Sue 762 Main tempi 46263 tempi@gmail.com


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

=A2&" "&B2&" "&C2

Drag/Fill Down to last paid record.

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

=A2&" "&B2&" "&C2

Drag/Fill Down to last record.

We now have two unique columns to compare
In Sheet1 H2

=IF(ISNA(MATCH(I2,Sheet2!G:G,0)),"","Paid")

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:=

=AND($F2="Not Paid",COUNTA($A2:$E2)>0)

Fill:= Red
Applies to:=

=$A$2:$F$22

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

Condition 2
Formula:=

=AND($F2<>"Paid",COUNTA($A2:$E2)>0)

Fill:= Yellow
Applies to:=

=$A$2:$F$22

(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.