How can I transpose and group data in Excel 2010?

1

2

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-.

Posted 2013-07-08T15:51:01.023

Reputation: 683

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 – 2013-07-08T16:12:39.897

This is not possible. – Fergus – 2013-07-08T16:27:57.853

1

See this: http://superuser.com/q/366616/76571 My answer can be adapted to fill columns rather than rows.

– Excellll – 2013-07-08T18:29:46.540

Answers

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

Posted 2013-07-08T15:51:01.023

Reputation: 1 682

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-. – 2013-07-09T08:27:11.077

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 – 2013-07-09T11:00:35.573

Asked: 2013-07-08T15:51:01.023

Viewed: 2 431 times

Active: 2013-07-08T16:37:23.477