Excel sorting question

ElJeffe

I'm trying to come up with a way of sorting some data, and I'm having trouble expressing what I want to do concisely enough to even Google it, so I'll ask y'all:

I have two columns of interest, ID number and age. One ID number can appear several times, each time with a different age. There are thousands of entries. I want the list sorted as follows:

Top entry is whatever has the oldest age in the list. Next, I want all remaining entries that have the same ID, sorted by age from oldest to youngest. Then I want the oldest age from whatever remains, and then all of the entries that have the same ID as that one sorted by age. And so on.

It needs to be something simple to implement, like a formula, and not some iterative process, because the target audience is not tech savvy at all.

Is there something simple I'm overlooking?

milski

Can't a pivot table do this?

E: actually probably still not.

tinwhiskers

Okay I think I have an approach, but am running out of time to test it, there might be some weird collisions.

1. Make a duplicate column of the IDcolumn

for the sake of easyness A=Age B=ID C=IDdup

Make Column D
{=MAX(IF(EXACT($B$1:$B$8,C1),$A$1:$A$8,0))}

it's an array function so ctrl+shift+enter

This makes column D the max age that that rows ID came in at

Make column E Concat(D1,C1)

So each E is XXXYYYYYYYY where XXX=AGE and YYYYYYYY=ID

Sort E decending

ElJeffe

Ooh, nice. I was trying to come up with something very similar to that, but I couldn't quite get it to work. I'll have to wait until tomorrow to test drive it, but it looks promising.