Sorting with formulas

[litrelord]

Is there any way of sorting data from one range into another so it fills the second range. I tried using RANK with some lookups but then I have problems if I have more than one item with the same value (and therefore the same rank number).

Any ideas?

Thanks

Nick

 

[NBVC]

What does your list look like now?

 

[litrelord]

As an example, I have

Mike
Dave
Tom
Bob

In column A and column B has test results pulled from individual sheets (e.g. 20%, 40%, 10%, 5%)

I want to have these cells hidden but have a similar table underneath which shows the same names but in the order of the test results.

It was working fine until two came back with identical % marks and then the INDEX/MATCH combination I was using to pull the name based on the rank column returned the same name for both of them (the first one it found). I’m trying to find a way round this.

Can’t use HTML maker at work I’m afraid otherwise I’d mock something up which would make it clearer.

Nick

EDIT: Just remembered - It has to be automatic updating so I can't use sort and I can't use macros because people will leave them switched off and moan when it doesn't work

 

[NBVC]

Perhaps something like this?

Book1
	A	B	C	D
1	Mike	20	3
2	Dave	40	1
3	Tom	10	4
4	Bob	40	2
5
6
7
8	Dave
9	Bob
10	Mike
11	Tom
12
Sheet1

The bottom part is the sorted list.

If you want largest to smallest just change SMALL to LARGE in the sort formula.

 

[litrelord]

Looks good my man. I shall try it out first thing tomorrow - provided I don't melt on the way home!

Thanks

Nick