Excel Spreadsheet Formulas for the Week
I'm in charge of the direct mailing at my office. Our leads come in via Excel sheets, which get fed through RoboAgent, and out pops an auto quote.
We recently had Database101 run our previous leads through their database to see if we could collect any phone numbers and/or emails.
We have 8 separate leads files, and 1 big amended file (with all the leads in no particular order). In order to get the quotes updated, I have to use the ReQuote feature, but wanted to update the individual leads files with phone/email. I opened the leads file, and copy/pasted the Amended file as Spreadsheet 2. So the Phone and Email columns were empty in Spreadsheet 1. Be sure that your columns are aligned! (ie if Phone column in Spreadsheet 1 is column C, then it better be C on Spreadsheet 2). I used the following 2 links for the formula, just adjusting the columns to match and transplant:
=IF(ISNUMBER(MATCH($C7, Sheet2!$C:$C, 0)), INDEX(Sheet2!H:H, MATCH($C:$C, Sheet2!$C:$C, 0)), "")
where C is the address field and H is the email field. Whatever data colum field you need drug in, switch out H. In my case, G was the phone, so I used this formula twice in my spreadsheet.
http://www.excelforum.com/excel-general/675712-merge-two-workbooks-copy-column-data-based-on-numerical-id-match-of-another-column.html
http://www.excelforum.com/excel-worksheet-functions/652821-multi-speadsheet-information-merge.html
Then I discovered that my phone numbers were written xxxxxxxxxx. Well, RoboAgent has to fill out the area code field and then the number. How to split the area code from the rest of the number? No problem. The easiest answer was Norie's:
Select the column, goto Data>Text to columns..., selected fixed width.
On the next step isolate the first 3 digits from the rest of the data.
http://www.ozgrid.com/forum/showthread.php?t=78312
It did split the column heading as "Pho" and "ne"--an easy thing.
We recently had Database101 run our previous leads through their database to see if we could collect any phone numbers and/or emails.
We have 8 separate leads files, and 1 big amended file (with all the leads in no particular order). In order to get the quotes updated, I have to use the ReQuote feature, but wanted to update the individual leads files with phone/email. I opened the leads file, and copy/pasted the Amended file as Spreadsheet 2. So the Phone and Email columns were empty in Spreadsheet 1. Be sure that your columns are aligned! (ie if Phone column in Spreadsheet 1 is column C, then it better be C on Spreadsheet 2). I used the following 2 links for the formula, just adjusting the columns to match and transplant:
=IF(ISNUMBER(MATCH($C7, Sheet2!$C:$C, 0)), INDEX(Sheet2!H:H, MATCH($C:$C, Sheet2!$C:$C, 0)), "")
where C is the address field and H is the email field. Whatever data colum field you need drug in, switch out H. In my case, G was the phone, so I used this formula twice in my spreadsheet.
http://www.excelforum.com/excel-general/675712-merge-two-workbooks-copy-column-data-based-on-numerical-id-match-of-another-column.html
http://www.excelforum.com/excel-worksheet-functions/652821-multi-speadsheet-information-merge.html
Then I discovered that my phone numbers were written xxxxxxxxxx. Well, RoboAgent has to fill out the area code field and then the number. How to split the area code from the rest of the number? No problem. The easiest answer was Norie's:
Select the column, goto Data>Text to columns..., selected fixed width.
On the next step isolate the first 3 digits from the rest of the data.
http://www.ozgrid.com/forum/showthread.php?t=78312
It did split the column heading as "Pho" and "ne"--an easy thing.
Comments
=LEFT($A2,3)
This assumes that the telephone number is in column 'A'. Copy the formula all the way down the page, then copy the whole column and paste values to remove the formula. You can then extract the right 7 characters the same way into another column by using
=RIGHT(A2,7)