1

I have rows of possible duplicate employee ID #'s, with another column of different values for the same person. I need a single column of unique ID's with the certifications transposed in the same row. For example: 

ID       Certification
0123     CPR
456      CPR
456      Nursing
456      Safety
789      Engineering
966      CPR
966      Safety

So for ID 0123 they only have one, there would only be one value to retrieve, but for ID 456, I would need the Excel sheet to have the Certification values in the same row for the ID but across as many times as the number of certifications there are: 

ID    Certif1        Certif2
456    CPR           Safety

Any ideas would be appreciated as the values are all text. I also am using Excel 10.

Oliver Salzburg
  • 89,072
  • 65
  • 269
  • 311
Kelly
  • 13

2 Answers2

1

You can build the transformed table in two steps.

Step 1: Generate list of unique IDs.

You can do this with the Advanced Filter tool. Select the column of IDs including the header and click Advanced Filter on the Data ribbon. Check the Unique Records Only checkbox, and choose to Copy to another location. Set the location for the output to go to and click OK. This will give you the list of unique IDs for the rows of your new table.

Step 2: Get certifications that match each ID in new columns.

You can use an array formula to return the matching certifications to the appropriate columns. In the row of the first record in your new table, enter the formula below, adjusted to match your sheet.

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$F2,ROW($B$1:$B$8),1000000),COLUMN()-6)),"")

where A1:B8 is the original data you provided,
F2 is the ID in the new table you are looking for certifications for, and
COLUMN()-6 equals 1 for the Certif1 column, 2 for Certif2 column (you'll have to adjust the subtraction term to match your data).

Once entered, select the cell, click inside the formula bar and press Ctrl+Shift+Enter. This will enter the formula as an array formula.

After doing this, fill the formula down the column. Then fill it over to as many columns as you need (just 3 for your sample data since 456 has 3 certifications). That should give you what you want.

Sample:

enter image description here

Community
  • 1
Excellll
  • 12,847
0

You could use a pivot table to build a tabular structure, though slightly different from the layout you describe.

enter image description here

For the example on the left, drag ID and Certification into the row area and select a tabular layout. Remove any totals and sub totals.

For the example on the left, drag ID to the row and Certification to the column area and Certification again to the Values area.

teylyn
  • 23,615