Question description:
This user has given permission to use the problem statement for this
blog.
How can I check for duplicate phone numbers across these 5 columns but still keep duplicates that are from one column?
Solved by A. C. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
04/05/2018 - 05:05
Hi JC
Excelchat Expert
04/05/2018 - 05:05
Hello :)
Excelchat Expert
04/05/2018 - 05:05
Thank you for sharing your data?
Excelchat Expert
04/05/2018 - 05:05
*!*
User
04/05/2018 - 05:05
Lots of data
Excelchat Expert
04/05/2018 - 05:06
Okay, so you would like to find the duplicates of A or Duplicates of B?
User
04/05/2018 - 05:07
dupicates acros A - E but not down A or down b ...
User
04/05/2018 - 05:08
A may have self and child with same number, but do not want ti listed for self more than once
Excelchat Expert
04/05/2018 - 05:08
What do you want to occur after duplicates are found?
User
04/05/2018 - 05:08
Delete them
Excelchat Expert
04/05/2018 - 05:08
Is this an ongoing solution, or just a onetime fix?
User
04/05/2018 - 05:09
one time
User
04/05/2018 - 05:09
cleaning up databse
Excelchat Expert
04/05/2018 - 05:09
Okay, sure. one moment, and I'll show you my prefered solution.
User
04/05/2018 - 05:09
great
Excelchat Expert
04/05/2018 - 05:10
https://docs.google.com/spreadsheets/d/1xmMd7RgMVZPHa2ag7iDNZOhP9QJccCYsRRZdieVUHwA/edit?usp=sharing
Excelchat Expert
04/05/2018 - 05:10
Please click on this link.
Excelchat Expert
04/05/2018 - 05:10
This will allow me to have larger screen to work with.
Excelchat Expert
04/05/2018 - 05:10
Is there a primary column?
Excelchat Expert
04/05/2018 - 05:10
For instance, if I find a value in column A that is in column B, which one gets deleted?
User
04/05/2018 - 05:11
Phone
User
04/05/2018 - 05:11
I need to paste the link?
Excelchat Expert
04/05/2018 - 05:11
No need to paste the link'
Excelchat Expert
04/05/2018 - 05:11
I just wanted you to click on it.
User
04/05/2018 - 05:11
It doesn't change the wcreen
Excelchat Expert
04/05/2018 - 05:11
It did for me.
Excelchat Expert
04/05/2018 - 05:12
That link is where I'm going to make the changes.
User
04/05/2018 - 05:12
hmmm
Excelchat Expert
04/05/2018 - 05:12
not on the sheet to the right because the window is double the size.
Excelchat Expert
04/05/2018 - 05:12
for me so that i can more easily work on this problem for you.
User
04/05/2018 - 05:12
ok
Excelchat Expert
04/05/2018 - 05:14
Am I comparing column B versus C?
Excelchat Expert
04/05/2018 - 05:14
or is it comparing A with B, C, D, E?
User
04/05/2018 - 05:14
A through E
Excelchat Expert
04/05/2018 - 05:15
Please, understand that there are many different ways you could compare.
Excelchat Expert
04/05/2018 - 05:15
If there is duplicates between C and D, then what happens.
Excelchat Expert
04/05/2018 - 05:15
If there are duplicates between A and E what happens.
User
04/05/2018 - 05:15
Okay. Just so all the duplicates in A stay in A, etc
User
04/05/2018 - 05:16
Delete them
Excelchat Expert
04/05/2018 - 05:16
Correct, you want duplicates to remain in A.
Excelchat Expert
04/05/2018 - 05:16
So is A the constant column?
User
04/05/2018 - 05:16
yes
Excelchat Expert
04/05/2018 - 05:16
Then, I compare column A with B, C, D, E.
User
04/05/2018 - 05:16
yes
Excelchat Expert
04/05/2018 - 05:16
In other words, I'm not comparing B and C with eachother?
User
04/05/2018 - 05:17
hmmm. There may be duplicates in B and C that we do not want
Excelchat Expert
04/05/2018 - 05:17
Sorry for all the questions, I just want to make sure I get the solution right the first time out of respect for your time.
Excelchat Expert
04/05/2018 - 05:17
So if B matches C what happens?
User
04/05/2018 - 05:17
I am completely on board with you
User
04/05/2018 - 05:18
I think a heiracrhy would work,
User
04/05/2018 - 05:19
If A keep but delte all others
User
04/05/2018 - 05:19
next if B keep and delete all others
Excelchat Expert
04/05/2018 - 05:19
Okay, understood, thank you.
User
04/05/2018 - 05:21
What happens if our time runs out?
Excelchat Expert
04/05/2018 - 05:21
Please extend the session
Excelchat Expert
04/05/2018 - 05:21
if the time runs out, the session will end.
Excelchat Expert
04/05/2018 - 05:21
I will be done shortly though.
User
04/05/2018 - 05:22
ok
Excelchat Expert
04/05/2018 - 05:27
Can you paste the phone-voice values in C2?
Excelchat Expert
04/05/2018 - 05:27
Nevermind, I can do it. Sorry.
User
04/05/2018 - 05:27
A and B did have tons of duplicates
Excelchat Expert
04/05/2018 - 05:28
Perfect, Thank you.
Excelchat Expert
04/05/2018 - 05:29
Okay, there you go, I used count if to find the duplicates.
Excelchat Expert
04/05/2018 - 05:29
Then, anything with duplicate to the right of it, you delete.
Excelchat Expert
04/05/2018 - 05:30
=if(countif($A$2:$A,C2)>0,"DELETE","")
Excelchat Expert
04/05/2018 - 05:30
That is the formula I used.
User
04/05/2018 - 05:30
Is there a way to simply copy and paste this?
Excelchat Expert
04/05/2018 - 05:30
That is what I did.
User
04/05/2018 - 05:30
ok
Excelchat Expert
04/05/2018 - 05:30
and I ran it through the whole sheet.
Excelchat Expert
04/05/2018 - 05:31
If you wanted to transfer to your file, you would have to first, make sure your columns are aligned.
User
04/05/2018 - 05:31
ok
Excelchat Expert
04/05/2018 - 05:31
Then, you would copy the formulas in D, F, H & J.
Excelchat Expert
04/05/2018 - 05:31
Just paste them in row 2.
Excelchat Expert
04/05/2018 - 05:32
then you can copy them, select D through J and paste the values down.
User
04/05/2018 - 05:32
Yes and them drag
Excelchat Expert
04/05/2018 - 05:32
Yup, excatly! :)
Excelchat Expert
04/05/2018 - 05:32
exactly*
User
04/05/2018 - 05:32
Thank you
User
04/05/2018 - 05:32
Good day
Excelchat Expert
04/05/2018 - 05:32
You're very welcome. Please be sure to rate after pressing "End Session"
Excelchat Expert
04/05/2018 - 05:32
You do the same :)
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.
