This is part of a series of Excel tips focused on helping you clean up your contact data. Today’s tip explains how to combine multiple columns into one.
We’re big fans of making it as easy as possible to import your data into Batchbook. We’ve done things like creating drag and drop import, imports from various integration partners and a handy mapping screen all in the name of helping you get up and running quickly.
Even with all of this, there is occasionally the need for folks to massage their data files in order for the smoothest transition from their old system (whether it’s another service or good ol’ Excel files) to Batchbook. When working with folks on cleaning up their data for import we’ve sometimes come across the need to combine multiple spreadsheet columns into one final column.
For example, we might have a customer who’s trying to import their client’s favorite colors as either tags or as a multi-choice Custom Field Set. In either of those cases we need to get all three columns into one column for easier importing.
Start by identifing which columns to combine
Here’s how the file looks when you start. Notice the three end columns, one for each available color.
Use find and replace to modify the column content
First, we’ll need to replace those ‘x’s’ under the color name with the actual color name and include a comma at the end. The commas are important because they tell Batchbook these are separate items.
So the ‘x’ in the blue column will now say ‘blue,’. To do that I’ll highlight one column by clicking on the letter at the top of the column, in this case “H”, and then use the ‘Find & Replace’ feature under the ‘Edit’ menu to add in the names. You’ll need to repeat this for each column.
Create a new column to hold the combined data
Once I’ve replaced all the x’s with the appropriate colors, I’ll add a new column with a header called ‘Color Preferences’ where I’ll be joining all the colors together in one cell.
Add the magic formula
In row 2 in my new Color Preferences column, I’ll put in the following formula:
which will be used to join the three columns together into one.
Tip: newer versions of Excel often have ‘formula builder’ helpers that can help in creating the formula as well.
Once the formula is in I’ll hit enter and see my new column showing ‘blue,green,purple,’.
Now all I need to do is add that formula to the rest of the cells in the column. I do that by clicking on the box with the formula and setting my cursor in the lower right corner of the box. Either double-click there or click and drag the box until you’re at the bottom row.
Turn the formula into normal text
We’re almost there. What you have now is a column full of formulas. To import this column into Batchbook, you need it to be text and not a formula.
Click on the letter at the top of the column to highlight the whole thing. Now with the column all nice and highlighted for you – right click and choose ‘Copy’ and then right click again and choose ‘Paste Special’. Select ‘Values’ from the list provided and hit OK. This will replace the formula cells with the actual value that the formula provided. It’s totally okay to just paste this over the formula column, or you can paste to a new column if you prefer.
Clean up and import
And now we’re really cooking! You can either remove those previous columns where the choices were split – or you can leave them as is and just skip those fields once you get to the import screen in Batchbook. You’re ready to get that import under way. If you’re new to imports, take a look at our import screencast for more details.
Any questions? Leave a comment below.