0

I have a database of several companies. There is one record per company. Within each company record, there are multiple contacts.

How can I create a record for each CONTACT (preserving all the same company information for each)?

Here is a sample:

Sample line of data - one company, multiple contact names/emails

  • 1
    Are these the exactly columns on your workbook? Your example shows what appears to be 4 contacts but 1 doesn't have an e-mail address field (column SM). Is that correct? – Twisty Impersonator Apr 17 '17 at 22:47
  • That was an example... and I accidentally hid the SM column. There are actually 6 or 7 email addresses and many other fields. I haven't purchased the database yet, but I want to be prepared to prepare it for import into my CRM when I do. – Morgan Wood Apr 18 '17 at 1:08
  • 1
    OK. I'm sure this can be done in Excel, but it would be easier in Access (assuming you know how to use Access). – Twisty Impersonator Apr 18 '17 at 1:23
  • I agree with Twisty. A database is the way to go. It's the single most common problem that everyone wants Excel to be a database, it's just that it's not. It's a spreadsheet with lots of "tricks". Basically you have to add an extra row beneath each contact and duplicate the information on each. In your example it's 3 contacts? So you'd need two rows inserted below and then duplicate (copy) the information down. But you want this automated right? If you're not a programmer, sometimes it's just a long task as most tasks in Excel is. – ejbytes Apr 18 '17 at 4:59
  • Another thing about keeping a database, which is what you are after, is all fields have to be the same for each Company or each record (row). What you show is a single record with multiple custom fields. A normal record for contact information is Name, Address, City, State, Zip, Phone, eMail, Alt Phone, Notes. Or similar. Like I said you give a fake sample so I don't know what type of record keeping skills you have. I'm pretty sure you'd have to just start cranking this out manually. One thing I do is start with small Macros (eg move down, insert two rows, move down 3 times... as start point). – ejbytes Apr 18 '17 at 5:11
2

This will suffice for your example. You will need to modify a bit to match the real database.

Example Data

Company | Address | City  | State | ZIP | Phone | GM | EmailGM | PD | EmailPD | SM | EmailSM | PRM | EmailPRM
ABC     | 1234 M  | Saint | MN    | zip | phone | gm | gm1@gm  | pd | pd1@pd  | sm | sm1@sm  | prm | prm@prm

Result

Company | Address | City  | State | ZIP | Phone | Name | Email
ABC     | 1234 M  | Saint | MN    | zip | phone | gm   | gm1@gm
ABC     | 1234 M  | Saint | MN    | zip | phone | pd   | pd1@pd
ABC     | 1234 M  | Saint | MN    | zip | phone | sm   | sm1@sm
ABC     | 1234 M  | Saint | MN    | zip | phone | prm  | prm1@prm

Company column (and Address, City, State, ZIP, Phone)
=INDIRECT("A"&(CEILING((ROW()+1)/4-1, 1)))

Name column (and Email)
=INDIRECT(CHAR(CODE("G")+MOD((ROW()-2), 4)*2)&CEILING((ROW()-1)/4+1, 1))

Explanation

At the Company column, the formula refers to "A" column and the row number is calculated from the current row.

ROW()-1 adjusts the row number back, because we have header in the data table. We use number 1 because the data table data starts at row 2, instead of row 1.

/4 + 1 basically copies the result row 4 times, then adjust the result row number forth by one, because we have header in the data table. We use number 4 because we have 4 names and emails. We use number 1 because the data table data starts at row 2, instead of row 1.

CEILING( ... , 1 ) rounds the row number up to an integer.


At the Name column, the formula refers to the "G" column and the row number is calculated from the current row.

(ROW()-2) shifts the result of MOD back to 0. We use number 2 because the result table data starts at row 2.

MOD( ... , 4) calculates which column to get. Result of 0 means column G, 1 means column H, and so on. We use number 4 because we have 4 names and emails.

+ ... * 2 shifts the column fetched to the right of column G by the result of modulo, multiplied by 2. We use number 2 because we have 2 columns, Name and Email, to fetch.

CODE("G") converts char "G" to its ASCII code.

CHAR( ... ) converts the value of shifted column (7 to 11, that is "G" to column "K", for example) back to string.

CEILING( ... ) gives the data row number to be fetched.

Instruction

Just change the column letter to the matching column.

Example:

for Address column, change the letter "A" to "B"

for Email column, change the letter "G" to "H"

Note

You mentioned you will have 7 names and emails. You will need to adjust the formula to use 7 instead of 4.

This formula is sensitive to where you put this formula. This formula assumes you will put it on row 2 (row 1 for header). You will need to adjust if you put it on row 1 (see Explanation)

This formula does not skip blank name and email. All companies will have exactly 4 rows, regardless of the number of name and email available.

This is not intended as a substitute to database, but you can use the data generated by this formula to create the database.

  • Wow. Thank you for the thoughtful and thorough response! I understand that Excel is less than optimal. I don't have access to Access - nor do I have the brain to figure out how to do it there either {perhaps I could hire someone...}. The list has a couple thousand names, EACH with up to 7 different contacts. However, in many of these organizations, the same contact may fill multiple roles, so then I'd have an issue of duplicates once I've separated the records. I am going to play with this formula and see if I can master it. THANK YOU SO MUCH! – Morgan Wood Apr 18 '17 at 21:53
0

Another way to do this, in Excel, is with Power Query or Data ► Get and Transform ► From Table (depends on version of Excel)

After getting into the query editor, select the columns that repeat, and UNPIVOT the other columns. This will give you a table like this:

enter image description here

Note that I renamed the new columns that were generated by the UNPIVOT operation.

Now add a third column, label it "Email" and enter this formula in I2

I2: =IF(ISERR(SEARCH("email",[@Position])),H3,"")

enter image description here

Then it is merely a matter of filtering out the Blanks from the Email column. You can do that with a simple filter; then copy/paste the visible cells to a results area; or you can use the Advanced Filter, which has a mechanism for sending the results of the filter to a designated area:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.