Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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". enter image description here

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):
enter image description here

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":enter image description here

On Step2a, select "I will create the page fields", and click "Next": enter image description here

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".

enter image description here

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". enter image description here

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

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): enter image description 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: enter image description here

share|improve this answer

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:

  1. Click somewhere in the data
  2. Ctrl-A (or manually select the data)
  3. Ctrl-G
  4. Alt-S
  5. Press K and then enter

You have just selected all the blank cells

  1. Press the equals key
  2. Press the up arrow on the keyboard
  3. Ctrl-enter

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

  1. Copy the whole lot again to a text document
  2. 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.

share|improve this answer
    
can you say me what are the names of the hotkeys, as my excel is not in english (is pt) –  Totty Mar 2 '11 at 10:34
    
Ctrl-A = Select All, Ctrl-G = Goto, Alt-S = Special, k = Blanks –  Hydaral Mar 2 '11 at 10:41
    
how to insert the new line character + a tab? i cant.i use notepad or eclipse ide –  Totty Mar 3 '11 at 9:52
    
Now im stuck at the 3th part. "press the up arrow" i lose my selection –  Totty Mar 3 '11 at 10:28
    
To do the search and replace, you will need a app that can use backslashed characters: \t for tab, \n for newline. I use Notepad2 –  Hydaral Mar 3 '11 at 14:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.