Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula that if I have an existing list of names and email addresses and then I add to the list, will remove the duplicate entries from the list but still have a separate list of new additions
Solved by D. Q. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/07/2018 - 06:51
Welcome, Thanks for choosing Got It Pro-Excel.
User 07/07/2018 - 06:52
Hello
Excelchat Expert 07/07/2018 - 06:53
A formula alone will not do that, but a macro will do.
User 07/07/2018 - 06:54
ok I don't know much about macros. Can you help?
Excelchat Expert 07/07/2018 - 06:55
Yeah, you mean you want to keep a list with duplicates and a list without duplicates, right?
User 07/07/2018 - 06:56
no .. I want the duplicate gone, but the new additions then put in a seperate list
User 07/07/2018 - 06:57
when I use a formula ... it removes the duplicates only in 1 column and leaves the email addresses
Excelchat Expert 07/07/2018 - 06:58
Do you have sample data so that you can illustrate this...please?
User 07/07/2018 - 06:58
Can I do it on the form right here?
Excelchat Expert 07/07/2018 - 06:59
Yeah, you can!
Excelchat Expert 07/07/2018 - 07:04
Obviously the added list will be towards the end of the original list. Why will you need this?
User 07/07/2018 - 07:05
ok the first list was added on 7/10. Then I add another list on 8/10 (highighted). Some are duplicates. I want to remove the duplicates (all info) but then seperate the 8/10 additions to another list or spreasheet
Excelchat Expert 07/07/2018 - 07:07
What makes the added list unique?
Excelchat Expert 07/07/2018 - 07:07
Is it different date it was added?
User 07/07/2018 - 07:08
I send out emails to the list but I don't want the recipient to possibly receive 2 emails or 4 or 5 as different lists are added. These are my clients who regularly buy or ask questions from me ... everytime they do .. they have to give me their email address
Excelchat Expert 07/07/2018 - 07:09
Okay.
Excelchat Expert 07/07/2018 - 07:09
How can we determine from what point a list has been added?
Excelchat Expert 07/07/2018 - 07:10
How do you usually determine?
Excelchat Expert 07/07/2018 - 07:15
I will create for you two sheets.
Excelchat Expert 07/07/2018 - 07:15
One will have the original data, which includes the duplicates.
Excelchat Expert 07/07/2018 - 07:16
The second will have added list added on a certain date.
Excelchat Expert 07/07/2018 - 07:16
The last one will have unique list of emails and individuals.
Excelchat Expert 07/07/2018 - 07:16
The email will be the primary key.
Excelchat Expert 07/07/2018 - 07:43
This is the sheet.
[Uploaded an Excel file]
Excelchat Expert 07/07/2018 - 07:43
I have uploaded the sheet.
Excelchat Expert 07/07/2018 - 07:43
It contains 3 sheets.
Excelchat Expert 07/07/2018 - 07:43
The one with Original data.
Excelchat Expert 07/07/2018 - 07:44
The one with added list based on a date.
Excelchat Expert 07/07/2018 - 07:44
And the one with unique values.
Excelchat Expert 07/07/2018 - 07:45
The one with Added list, you simply enter the date of the list you want to see in cell B2 of "Added List" sheet.
Excelchat Expert 07/07/2018 - 07:46
For example you can type 7/8/2018 and it will automatically give you a list of the added names and their emails.
Excelchat Expert 07/07/2018 - 07:47
The formula used in cell B5 of Added List sheet is an index formula.
Excelchat Expert 07/07/2018 - 07:47
=INDEX(Original!$B$2:$B$14, MATCH(0, IF($B$2=Original!$C$2:$C$14, COUNTIF($B$4:$B4, Original!$B$2:$B$14), ""), 0))
Excelchat Expert 07/07/2018 - 07:47
Sorry I mean an array formula rather than an index formula.
Excelchat Expert 07/07/2018 - 07:47
For an array formula to work, you will press CTRL + SHIFT + ENTER
Excelchat Expert 07/07/2018 - 07:48
The formula works for the range just given, i.e. A2:B14 of Original sheet.
Excelchat Expert 07/07/2018 - 07:49
But you will change this range so that it captures all your data.
Excelchat Expert 07/07/2018 - 07:50
Similarly to array formula in B2 of Unique List sheet.
Excelchat Expert 07/07/2018 - 07:50
=INDEX(Original!$B$2:$B$14,MATCH(0,COUNTIF($B$1:B1,Original!B$2:$B$14),0))
Excelchat Expert 07/07/2018 - 07:51
You just need to drag the cells down so that you get a full list in the case you have added other names.
Excelchat Expert 07/07/2018 - 07:51
I see time is over.
Excelchat Expert 07/07/2018 - 07:51
We can no longer chat.
Excelchat Expert 07/07/2018 - 07:51
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help. Enjoy the rest of your day!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.