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")
While other sources export their data like this (Let's call this representation FORMAT "B")
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...
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 of these sources export their data like this (Let's call this representation FORMAT "A")
| Full Name | Mobile Phone | Work Phone | Home Phone | ||||
| Jackson Browne | (704) 341-3010 |
|
| ||||
| Mark Knopfler | (510) 524-4253 |
|
| ||||
| David Lee Roth | 490-555-1313 |
|
While other sources export their data like this (Let's call this representation FORMAT "B")
| Full Name | Phone 1 - Type | Phone 1 - Value | Phone 2 - Type | Phone 2 - Value |
| Ralph Bellamy | Mobile | 555-867-5309 | Home | 425-365-8180 |
| Eddie A. T. Cruiser | Work | 202-544-3900 | ||
| Claude Rains | Work | 330-491-3235 | ||
| Donald Pleasance | Home | 272-888-3131 | Mobile | 703-757-8281 |
| Strother Martin | Mobile | 571-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 - Type | Phone 1 - Value | . . . |
| Ralph Bellamy | =IF(E2="Mobile", F2, "") | =IF(E2="Work", F2, "") | =IF(E2="Home", F2, "") | Mobile | 555-867-5309 | . . . |
| Eddie A. T. Cruiser | =IF(E3="Mobile", F3, "") | =IF(E3="Work", F3, "") | =IF(E3="Home", F3, "") | Work | 202-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
