I have a sheet that has 2 cols; in one is the name, in the other there are one or more emails, separed by comma

Question

I have an Excel worksheet that has two columns, in one is the name, in the other there are one or more emails, separated by a comma.

Now, I want to get two columns with repeated names but unique emails, so no more than one email address in a single cell, I just want column one to have a name and column two to have an email.

Now it is like this:

name1    email1, email2, email3
name2    email1
name3    email1, email2

And I want it like this:

name1    email1
name1    email2
name1    email3
name2    email1
name3    email1
name3    email2

thanks

Answer 1

You appear to be 'de-pivoting' a pivot table. Here's another approach based on John Walkenbach's very cool tip: Creating a database table from a summary table . I use this tip all the time, and it is a real time-saver.

If you are using Excel 2007 or higher, I strongly suggest you add the Pivot table button to your Quick Access Toolbar. This will permit you to access the "old" Excel 2003 pivot table Wizard. Being able to use the "old" Wizard is very important! Another way to bring up the old pivot table wizard is to type "ALT+D" , then "P".

Now, to your specific issue. Use the "Text-to-columns" feature of the Data menu to split your 2nd data column at the commas. You must also add some column headings. (I have rebuilt your sample data to show unique email addresses for a bit more clarity):

Click on the Pivot table button that is on your Quick Access toolbar. It will bring up Step 1 of the old Wizard. Select the "Multiple consolidation ranges" button, and click "next":

On Step2a, select "I will create the page fields", and click "Next":

On Step2b, highlight your data range, then click the "add" button. This will copy your data range into the lower window of the form. Click "Next".

On step 3, you can choose "New Worksheet" or "Existing Worksheet" as long as the cell you select doesn't overlap your source data. Click "finish".

You should now have a pivot table, and the Pivot Table Field List will be displayed:

Un-check the "Row" box, and the "Column" box, so that you are left with a very small pivot table in a single cell (shown in cell A4 here):

Here's the really cool part: DOUBLE-CLICK that single cell. Your pivot table gets 'de-pivoted' onto a new sheet. You can now filter out the blanks in the third column, and you'll get the layout you want:

Answer 2

Personally, I would do the following:

1. Sort the worksheet by column A
2. Copy the whole list to a text editor
3. Search and replace "[comma][space]" with "[newline][tab]"
4. Copy the whole lot back to Excel

Now this is a trick I just learned:

5. Click somewhere in the data
6. Ctrl-A (or manually select the data)
7. Ctrl-G
8. Alt-S
9. Press K and then enter

You have just selected all the blank cells

10. Press the equals key
11. Press the up arrow on the keyboard
12. Ctrl-enter

This will ensure that blank cells have a value of the cell above (this is especially useful when copying pivot tables)

13. Copy the whole lot again to a text document
14. Copy it back to Excel

That ensures that the cells are no longer using formulas.

If this is something that you need to do regularly, I would suggest Awk.