Merging contact info from different exports (sticking point: phone/address/email type column + 1 data column vs. multiple data columns)

sebby_joe

New Member
Joined
Nov 24, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm working with some seasoned realtors that are looking to combine their client data for an import into their CRM, one realtor has data that is stored in iCloud, Yahoo, and Gmail, which are exported to separate CSV files. The trouble is that the exported phone numbers, email addresses, and mailing addresses are represented in two different ways among the CSV files, which have been giving me trouble. You'll see what I mean through the examples.

Some of these sources export their data like this (Let's call this representation FORMAT "A")

Full NameMobile PhoneWork Phone Home Phone
Jackson Browne(704) 341-3010
240-273-8810
(813) 927-0128
Mark Knopfler(510) 524-4253
(202) 270-5746
David Lee Roth490-555-1313
555-234-0258


While other sources export their data like this (Let's call this representation FORMAT "B")

Full NamePhone 1 - TypePhone 1 - ValuePhone 2 - TypePhone 2 - Value
Ralph BellamyMobile555-867-5309Home425-365-8180
Eddie A. T. CruiserWork202-544-3900
Claude RainsWork330-491-3235
Donald PleasanceHome272-888-3131Mobile703-757-8281
Strother MartinMobile571-213-4499


Given that there are thousands of contacts in each format, how would I go about combining these spreadsheets in the style of Format A (the CRM requires data to look like Format A for import)?

The makeshift solution that I came up with is to add columns to extract out each phone type by the use of "if" statements...

A​
B​
C​
D​
E​
F​
Full Name (A1)Mobile Phone (added column)Work Phone (added column)Home Phone (added column)Phone 1 - TypePhone 1 - Value. . .
Ralph Bellamy=IF(E2="Mobile", F2, "")=IF(E2="Work", F2, "")=IF(E2="Home", F2, "")Mobile555-867-5309. . .
Eddie A. T. Cruiser=IF(E3="Mobile", F3, "")=IF(E3="Work", F3, "")=IF(E3="Home", F3, "")Work202-544-3900. . .
................ . .

Of course, I'll paste the values of each sell over the "IF"-statement formulas for the added columns so that I can delete the corresponding Phone-Type & Phone-Value columns.

I'd do this for each Phone-Type/ Phone-Value pair for mobile, work, and home phones. So I'll have multiple columns for each that I'll have to combine after. On top of this, I have to do this same process for email addresses and mailing addresses. After I've gotten Format B for look like Format A by the method above, I'll use a power query/Get & Transform to combine the data from the different spreadsheet exports.

There must be a better way that I'm missing. I know that I can get the job done with the method that I described, but it hurts my soul to be so inefficient. Has anyone run into this before?

Thank you!
~Sebby-Joe
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sebby_joe

New Member
Joined
Nov 24, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Could anyone point me in the right direction on my problem that I still haven’t solved? if not, could someone educate me on how I can ask my question better; was I too wordy?

Any assistance is appreciated 🙏
 

Watch MrExcel Video

Forum statistics

Threads
1,127,536
Messages
5,625,390
Members
416,099
Latest member
sudarsan23

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