1

I don't think I'm phrasing the question correctly so I'd be pleased if someone could edit it to something sensible!

I'm using Excel 2010 and I have a set of data like this:

Art 12
Bob 15
Bob 18
Kev 16
Kev 13
Kev 14
Kev 20
Deb 12
Deb 21

I want to turn it into this:

Art Bob Kev Deb
12  15  16  12
    18  13  21
        14  
        20

The paste-special transpose option doesn't do quite what I want but it is close. It gives me this:

Art Bob Bob Kev Kev Kev Kev Deb Deb
12  15  18  16  13  14  20  12  21

How can I transform the data in the way I need?

Thank you

G-.
  • 743

1 Answers1

1

If this is a one-time process, I'd do it like so:

Add columns to the right for the various names (C:F). If you have a long list, I'd first copy the whole column elsewhere and use the "Remove Duplicates" Data Tool from the Data ribbon. I would then put the following equation in those columns:

=IF($A2=C$1,$B2,"")

The results would be as follows for the example:

Agent | Value |  Art | Bob | Kev | Deb
Art   |    12 |   12 |     |     |              
Bob   |    15 |      |  15 |     |
Bob   |    18 |      |  18 |     |
Kev   |    16 |      |     |  16 | 
Kev   |    13 |      |     |  13 | 
Kev   |    14 |      |     |  14 | 
Kev   |    20 |      |     |  20 | 
Deb   |    12 |      |     |     |  12
Deb   |    21 |      |     |     |  21

Copy the new columns (C:F) and paste in a new worksheet, but paste as values. Then sort each column individually, choosing not to expand the selection each time.

If you have a ton of columns, I'd probably set up a macro for the column-sorting step. If you're going to have to do this regularly, it might pay off to create some clever equations or even just a giant macro to go through these steps.

As I asked in my comment, what are you going to do with this data after it is reformatted?

Dane
  • 1,945