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.
