Excel Help - Manipulating some data

General Questions
#1

I have a report that isn’t formatted very well for actually getting any information out of the report. I’ve shown an example of how the thing is formatted below.



          Category1        Category4
          Category2        Category5
Job #     Category3        Category6
----------------------------------------------
123456    Excellent        Poor
          Excellent        Fair
          Excellent        Good

This is repeated for 124 job numbers. What I’m looking for is an easy way to get the data into the following format:



Job #      Category1      Category2      Etc...
------------------------------------------------------
123456     Excellent      Excellent      Etc...

Any excel guru’s out there know of an easy way to do this?

#2

It’s not elegant, but the best way is probably creating formulas in the adjacent cells.

If the first row of data is in row 4, and your original data is in columns A-C, then enter the following formulas:


in cell D4: =B4
        E4: =B5
        F4: =B6
        G4: =C4
        H4: =C5
        I4: =C6

The copy those cells down through all the rows. Copy columns D through I, Paste Special - values only, then you can delete original columns B and C.