Matching two spreadsheets via one matching column
June 3, 2013 9:39 AM Subscribe
I have two separate marketing lists on spreadsheets, both of which were originally opt-in, but have been residing on different systems - System A for email marketing and System B for SMS marketing. List A has the users' email addresses, cellphone numbers and names. List B just has the users' cellphone numbers (plus a column called "unsubscribed" for those people who. I want to combine the two spreadsheets to create one spreadsheet while keeping the column called "unsubscribed" so I know which people to keep permanently removed. Can you advise me on how to synchronise these two spreadsheets using the cellphone number as the common data? Thanks!
Adding on to Ruthless Bunny's comment, make sure your phone numbers match in format or more preferably, remove all formatting and let Excel do the work. The reason is that even an errant space will throw off the lookup.
I would go to each phone column and search and replace any characters like "(", ")", ".", "-", spaces and anything else with no character. You can do this with the normal Search & Replace function. If there are other characters like "/", that goes too. What you are shooting for is just to have the numbers with no other characters or spaces. There are some utilities that can help with this, but this one the way to go without new software.
Then use Excel's built in formatting function (Format Tab, usually at the bottom) under the Category: Special) choose to format as Phone Number. In reality, this is just a display function. The actual data will remain just the text.
Now do the lookup as RB described.
posted by lampshade at 10:00 AM on June 3, 2013 [1 favorite]
I would go to each phone column and search and replace any characters like "(", ")", ".", "-", spaces and anything else with no character. You can do this with the normal Search & Replace function. If there are other characters like "/", that goes too. What you are shooting for is just to have the numbers with no other characters or spaces. There are some utilities that can help with this, but this one the way to go without new software.
Then use Excel's built in formatting function (Format Tab, usually at the bottom) under the Category: Special) choose to format as Phone Number. In reality, this is just a display function. The actual data will remain just the text.
Now do the lookup as RB described.
posted by lampshade at 10:00 AM on June 3, 2013 [1 favorite]
what they said. it's pretty easy once you get the hang of it.
posted by jpe at 5:59 PM on June 3, 2013
posted by jpe at 5:59 PM on June 3, 2013
Vlookup is the best way, as everyone has said and it does not require a Named Range. I always just throw the tab name and column references in there: Sheet2!A:B so the formula becomes =VLOOKUP(B2,Sheet2!A:B,2,false).
If you have a lot of records and Excel has to keep pausing to calculate, I would make a separate list of just the unsubscribe numbers. Then you can do a CountIf to see which of those are on your main page.
If you know that every record in your unsubscribe sheet should have a record in your main sheet, be sure to count the results to make sure you caught them all. Like lampshade says, you will need to make sure all your phone numbers are free of parenthesis, periods, hyphens and spaces or you could miss one. The TRIM() function will remove any trailing spaces but keep internal ones, so it maybe helpful when cleaning up your data.
posted by soelo at 7:59 PM on June 3, 2013
If you have a lot of records and Excel has to keep pausing to calculate, I would make a separate list of just the unsubscribe numbers. Then you can do a CountIf to see which of those are on your main page.
If you know that every record in your unsubscribe sheet should have a record in your main sheet, be sure to count the results to make sure you caught them all. Like lampshade says, you will need to make sure all your phone numbers are free of parenthesis, periods, hyphens and spaces or you could miss one. The TRIM() function will remove any trailing spaces but keep internal ones, so it maybe helpful when cleaning up your data.
posted by soelo at 7:59 PM on June 3, 2013
« Older Help me define a new job title! | Non-partisan, non-profit. How non-partisan do I... Newer »
This thread is closed to new comments.
First of all make the common element, in this case the Cell Number as the first column in the spreadsheet with the Subscribe/Unsubscribe. Highlight the data in both columns and in the Name Range section of the spreadsheet call it Subscription.
Then create a new column on the spreadsheet, be sure to highlight it and set the data type as General. Assuming that the Cell Phone Number on this spreadsheet is in column B, write the formula: =VLOOKUP(B2,Subscription,2,false)
Copy and paste the formula down the entire column. Like magic you'll get your Subscribed/unsubscribed info. Sort and remove the unsubscribed.
When done, copy and paste special values. Remove any #N/A or 0 from the list.
How ya like me now?
posted by Ruthless Bunny at 9:45 AM on June 3, 2013 [2 favorites]