Excel data in rows and columns to be reformatted

microsoft excelmicrosoft-excel-2007worksheet-function

I have data in Excel in a staggered format. There is a customer ID in the left-most column and the details of that customer are in different rows within different columns. I need to rearrange the data into one single row for each customer. It is in the same format for all other customers. 

CustomerID FirstName LastName Education Training TrainingYear City State Zip
1          John       C       Masters
1                                      SAP
1                                                2017
1                                                             LA
1                                                                   CA
1                                                                        91433
2          Max        G       Bachelors
2                                       Oracle
2                                               2015
2                                                             NR
2                                                                  CA
2                                                                        91234

Best Answer

Use this

=INDIRECT(CONCATENATE("A", ROW()*6-10))

Changing "A" to the corresponding column (B for Firstname, C for Lastname, D for Education, and so on) and changing "-10" to the corresponding column (-10 for Education, -9 for Training, -8 for TrainingYear, ... , -5 for Zip)

For Training column

=INDIRECT(CONCATENATE("E", ROW()*6-9))

For Zip Column

=INDIRECT(CONCATENATE("H", ROW()*6-6))

Credits to fixer1234 comment.

This intuitive method does NOT work (or at least not as convenient, because AutoFill does not recognize the simple 6 step pattern):

CustomerID | Firstname | Lastname | Education | Training | TrainingYear | City | State | Zip
----------------------------------------------------------------------------------------------
=A2        | =B2       | =C2      | =D2       | =E3      | =F4          | =G5  | =H6   | =I7
=A8        | =B8       | =C8      | =D8       | =E9      | =F10         | =G11 | =H12  | =I13
Related Question