Sorting with formulas

litrelord · Jul 19th, 2006, 05:39 PM

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 · Jul 19th, 2006, 05:50 PM

What does your list look like now?

litrelord · Jul 19th, 2006, 05:56 PM

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 · Jul 19th, 2006, 06:08 PM

Perhaps something like this?

******** ******************** ************************************************************************>

Microsoft Excel - Book1

___Running: 11.0 : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

=

A

B

C

D

1

Mike

20

3

2

Dave

40

1

3

Tom

10

4

Bob

40

2

5

6

7

8

9

10

11

12

Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The bottom part is the sorted list.

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

litrelord · Jul 19th, 2006, 06:09 PM

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

Thanks

Nick

Jul 19th, 2006, 05:39 PM	#1
litrelord Join Date: Dec 2002 Posts: 502	Sorting with formulas 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

Jul 19th, 2006, 05:50 PM	#2
NBVC Join Date: Aug 2005 Location: Ontario Posts: 5,260	What does your list look like now? __________________ Where there is a will there are many ways. Finding one that works for you is the challenge! Colo's HTML Maker: Instructions

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Jul 19th, 2006, 05:56 PM	#3
litrelord Join Date: Dec 2002 Posts: 502	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

Jul 19th, 2006, 06:09 PM	#5
litrelord Join Date: Dec 2002 Posts: 502	Looks good my man. I shall try it out first thing tomorrow - provided I don't melt on the way home! Thanks Nick