Problematic Auto-sort

Thanks for the help. I'm trying to auto-sort based on a data table.

In the attached sheet I've got a row of names (G1:AE1) and they have various types (AA, BB, CC, DD) and I'd like them sorted by the values in row 29, and for that to appear in (AA)A2:A12, (BB)B2:B12. The type is static, so if you help with the formula I can fix it for type (if I understand it).

I'd also like it to auto sort, so I don't have to sort each time the data repopulates.

Is this possible?

Thanks,

uttuck

Hi

A2: =IFERROR(SMALL(IF(RIGHT($G$1:$AE$1,2)=A$1,$G$29:$AE$29,""),ROW()-1),""). This formula is array entered (ctrl, shift, enter).

Copy down / across as required.

rylo

Rylo,

Thanks, so close. Everything is right, except this displays their score, and not their name. Is there a way to display the name (from cells G1) instead of the score (from G29) while keeping everything else the same?

Thanks again, that is a really impressive formula.

uttuck

And is it possible to sort from Big to Small, not small to big? Thanks so much.

uttuck

Hi

Sorting from big to small is easy. Change the SMALL to LARGE.

Try this (array entered) to get the name.

=IFERROR(INDEX($1:$1,SUMPRODUCT(--($G$29:$AE$29=LARGE(IF(RIGHT($G$1:$AE$1,2)=A$1,$G$29:$AE$29,""),ROW()-1)),--(RIGHT($G$1:$AE$1,2)=A$1),COLUMN($G$1:$AE$1))),"")

rylo

Thanks so much rylo. Worked like a charm. I really appreciate it. You totally saved me.

uttuck