Spreadsheet Question

[pgrmdave]

So, I'm looking for a way to take data from one location and have it appear in another location sorted. Here's an example:

 

Given:

| 453 | Abby    
| 301 | Beth     
| 589 | Cathy
| 568 | David
| 623 | Edward

 

I want to return, in different cells:

Edward
Cathy
David
Abby
Beth

 

 

Is there a way to do this without using a macro?

[alexander]

isn't there a sort function you can use to do something like that?

[Donk]

You can do it this way if you're ok with using an intermediate column:

D1: =LARGE(A1:A5,1)
D2: =LARGE(A1:A5,2)
D3: =LARGE(A1:A5,3)
D4: =LARGE(A1:A5,4)
D5: =LARGE(A1:A5,5)

Then
E1: =IF(A1=D1,B1,IF(A2=D1,B2,IF(A3=D1,B3,IF(A4=D1,B4,IF(A5=D1,B5)))))
E2: =IF(A1=D2,B1,IF(A2=D2,B2,IF(A3=D2,B3,IF(A4=D2,B4,IF(A5=D2,B5)))))
E3: =IF(A1=D3,B1,IF(A2=D3,B2,IF(A3=D3,B3,IF(A4=D3,B4,IF(A5=D3,B5)))))
E4: =IF(A1=D4,B1,IF(A2=D4,B2,IF(A3=D4,B3,IF(A4=D4,B4,IF(A5=D4,B5)))))
E5: =IF(A1=D5,B1,IF(A2=D5,B2,IF(A3=D5,B3,IF(A4=D5,B4,IF(A5=D5,B5)))))

A nasty piece of coding - worse if you have more than five items in the list!

 

You can do it without the intermediate column. Replace D1 with LARGE(A1:A5,1), D2 with LARGE(A1:A5,2) and so on. This gives

 

E1: =IF(A1=LARGE(A1:A5,1),B1,IF(A2=LARGE(A1:A5,1),B2,IF(A3=LARGE(A1:A5,1),B3,IF(A4=LARGE(A1:A5,1),B4,IF(A5=LARGE(A1:A5,1),B5)))))

 

and you can work out the rest for yourself :evil:

[pgrmdave]

Hmmm, right now I'm using this:

 

=MAX(IF(ISNA(MATCH($G$5,$D$12:D13,0)),$G$5,0),IF(ISNA(MATCH($G$6,$D$12:D13,0)),$G$6,0),IF(ISNA(MATCH($G$7,$D$12:D13,0)),$G$7,0),IF(ISNA(MATCH($G$8,$D$12:D13,0)),$G$8,0),IF(ISNA(MATCH($G$9,$D$12:D13,0)),$G$9,0))

 

Which basically finds the max of all the numbers that don't exist in the list above it.

 

And I can't find a 'sort' function that doesn't just sort the original data. So far, it looks like I just need to deal with some really ugly formulas...

[alexander]

this would be tough without a macro... :doh:

[Karnuvap]

Closest I can get you is reverse order Sorry.

 

Assuming your numbers are in column E and your Names are in Column F then this formula creates a column with the names in reverse order ...

=CHOOSE((6-RANK(E1;E$1:E$5));F$1;F$2;F$3;F$4;F$5)

Unfortunately you will need to adjust the ranges according to how many names you have (this is set for your five) and the 6 becomes n+1 where n=the number of entries in your list. The cell list to choose from is limited to 30 entries in the version I am using.

 

Sorry its not what you were after but I couldn't find a way to get reverse RANKing .

 

The Vap.