DaRTH KiRo

New Member
Joined
Jan 24, 2018
Messages
32
I am kind of just brainstorming here. I have a massive list of contacts that I am try to clean to the best of my ability before handling an import. I have Account Numbers in column A and First / Last in B & C, column D are phone numbers & column E are extensions, and finally column F is the email address. Would there be a way to compare Account Numbers and email addresses and when it hits a duplicate of both it moves the value from D, or maybe even D & E, into Column H? I am running into contacts that have multiple phone numbers and I cannot be sure which is accurate and I would like to not have to delete them. I believe I may be able to doctor something up on my own (maybe?) if it was only just one duplicate but there are times where one account number could have two or more duplicate email addresses (due to multiple phone numbers). The columns would be sorted so that the emails are in alphabetical order and then the account numbers would be sorted from smallest to largest so technically all matching email addresses for the specific account number would be together.

If you are reading this, thank you for your time! This forum saves me so much time and manual work!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
Create two additional columns - first one concatenating the Account Number and email address and the second one, a countif, counting how many occurrences of the concatenated Account Number and email address values are in your massive list, then you can filter them, where the contif value is greater than 1 and take whatever your next step is to verify values

Excel Forum.xlsx
ABCDEFGH
1Account NumbersFirst LastPhone NumberExtensionsemail addressConcatCount
2123456Doesn'tMatter0131 123 4567IrrelevantmrExcel1@Excel.com123456mrExcel1@Excel.com1
3123457Doesn'tMatter0131 123 4567IrrelevantmrExcel2@Excel.com123457mrExcel2@Excel.com1
4123458Doesn'tMatter0131 123 4567IrrelevantmrExcel3@Excel.com123458mrExcel3@Excel.com1
5123459Doesn'tMatter0131 123 4567IrrelevantmrExcel4@Excel.com123459mrExcel4@Excel.com1
6123460Doesn'tMatter0131 123 4567IrrelevantmrExcel5@Excel.com123460mrExcel5@Excel.com2
7123460Doesn'tMatter0131 123 4567IrrelevantmrExcel5@Excel.com123460mrExcel5@Excel.com2
8123462Doesn'tMatter0131 123 4567IrrelevantmrExcel7@Excel.com123462mrExcel7@Excel.com1
9123463Doesn'tMatter0131 123 4567IrrelevantmrExcel8@Excel.com123463mrExcel8@Excel.com1
10123464Doesn'tMatter0131 123 4567IrrelevantmrExcel9@Excel.com123464mrExcel9@Excel.com1
11123465Doesn'tMatter0131 123 4567IrrelevantmrExcel10@Excel.com123465mrExcel10@Excel.com1
Sheet6
Cell Formulas
RangeFormula
G2:G11G2=A2&F2
H2:H11H2=COUNTIF($G$2:$G$11,G2)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,252
Messages
5,641,113
Members
417,194
Latest member
Excellent Excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top