I am currently working with a spreadsheet that has a unique student id as the first heading. Each student has one or more emergency contacts. Each emergency contact has their own set of phone numbers. So if a student has two emergency contacts he/she will have two lines in the spreadsheet. I need each student to be on only one line, while preserving the unique phone numbers into their own cells. So instead of having two entries for the same student under "Home phone" I would like there to be a "Home phone_1" and "Home phone_2." I am an excel beginner and any help would be greatly appreciated. Thank you.

  • Is this a one-off thing or something you'll be doing routinely? – airstrike Oct 14 '16 at 18:16
  • It should only be a couple of times a year. I'm going to have to do this again for the staff. There are a total of about 8000 lines. – user2884621 Oct 14 '16 at 18:20

I'm going to assume you have only two columns: Id and Phone. You'll need to create a helper column that counts how many occurrences of a given Id there are in every row above the current row. Here's what that's going to look like:

enter image description here

Notice that I added a "Home Phone_"& to the start of the formula in C2 to match the desired output in your question. Feel free to change that.

Also notice that A$1:A1 has to be locked in row 1 (that's what the $ is for -- you can toggle locks manually or by pressing F4 when you enter a formula).

The formula in C2 has to be copied down all the way to the last row in the table.

Next, we're going to select our entire data (including column C) and create a Pivot Table by going to Insert > Pivot Table (or pressing Alt N V T).

enter image description here

Hit Ok on the next dialog box, then on the pivot table, drag Id to Rows, Phone to Values, and N to Columns.

Here's what the result should look like:

enter image description here

You can remove the totals by playing around with the commands available in the Pivot Table menu in the Ribbon.

EDIT -- If you have more than one column, you'll want to make the N column just a number and the layout of the Pivot Table changes a bit.

enter image description here

And now the Pivot Table:

enter image description here

Notice that the Σ Values column is above N in the pivot fields pane. Additionally, please refrain from editing the names of the columns directly in the cells (i.e. changing Sum of Office Phone to Office Phone). You'll instead want to click on the arrow on Sum of Office... in the Values pane and change the name there, as well as edit the number formatting.

| improve this answer | |
  • That looks great. Heading home in a few minutes so don't have time to test today but will in the morning. I actually do have more fields I need to do this with (mobile phone, work phone, etc). Can I just repeat these steps to get it working for all fields I need? – user2884621 Oct 14 '16 at 18:46
  • @user2884621 I've edited the answer to show you how to do that for multiple fields. Hope that's easy enough to follow. Good luck! – airstrike Oct 14 '16 at 18:55
  • I am making progress with this and it seems to be doing what I need. However I am running into an issue that instead of showing the individual phone numbers in the pivot table, all of the numbers are returning as a 0, not the 10 digit phone number. – user2884621 Oct 17 '16 at 13:48
  • @user2884621 that's because your phone numbers will need to have only numbers in them, with no dashes or parens. you can apply formatting to the cells to make them look nicer after the fact – airstrike Oct 17 '16 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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