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

3
  • I'm really curious about this, since I can't think of any elegant feature that Excel has to do this. The transposition isn't the problem. It's the way you're grouping things that is unconventional. What are you going to do with the data after it's grouped like that? Maybe we can help get you to that point without the precise step you're attempting.
    – Dane
    Jul 8, 2013 at 16:12
  • This is not possible.
    – Fergus
    Jul 8, 2013 at 16:27
  • 1
    See this: superuser.com/q/366616/76571 My answer can be adapted to fill columns rather than rows.
    – Excellll
    Jul 8, 2013 at 18:29

1 Answer 1

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?

2
  • I've not yet tried your solution, but I am trying to generate a Box & Whisker diagram to present the variation in page load times from data grabbed from IIS logs. The guide I followed to making these diagrams in Excel had source data presented this way. I'm pretty sure this isn't the best way to do it, but I thought it an interesting question / challenge!
    – G-.
    Jul 9, 2013 at 8:27
  • You can probably skip the copy-paste and column sorting. Functions like STDEV, QUARTILE, AVERAGE, SUM, COUNT, MIN, MAX, and MEDIAN can work off of the columns as presented (with blank rows above the start of data for the given column).
    – Dane
    Jul 9, 2013 at 11:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .