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.
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:
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
Ok on the next dialog box, then on the pivot table, drag
Here's what the result should look like:
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.
And now the Pivot Table:
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.