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.
2 comments

Popular posts from this blog

2015 Ford Fusion Replacement Rear Deck Subwoofers

Robert Stroud Gravesite Exact Location (Birdman of Alcatraz)

Shark Navigator Swivel LEAD WARNING