LARGE with second/third array in case of a tie ?

**KomicJ** · 12-20-2016, 03:10 PM

Hey guys,

I'm trying to use a LARGE function (at least, I think it's the right function to use) that would kinda act like a 'sort'...but need to add a second array (and even possibly a third array) to be used in case of a tie in the first one.

Basically, first filter should be $F$5:$F$14 (Largest to smallest), second filter $G$5:$G$14 (Largest to smallest). If a third array is possible, then $B$5:$B$14 (Alphabetically).

Any ideas ?

**sandy666** · 12-20-2016, 04:34 PM

is that what you want?

Formula:

Please Login or Register  to view this content.

confirmed by Control+Shift+Enter

adjust ranges to your needs

**KomicJ** · 12-20-2016, 04:59 PM

Not really, unless I'm applying it wrong...

Here, I attached a xls sheet to show what I'm trying to do exactly.

Basically, I'm trying to use an INDEX/MATCH function in Column I to return the names from Column B and sort them by PTS (Column F), then HITS (Column G) if PTS are equals, and then NAMES (Column B) if PTS and HITS are equals...
...all using a LARGE function, though I'm not entirely sure it's the best way to go. In fact, I'm not entirely sure what I'm trying to achieve can be done by a formula.

**sandy666** · 12-20-2016, 06:10 PM

You mean: =IF(PTS=PTS,LARGE(HTS,ROW(1:1)),IF(AND(PTS=PTS.HTS=HTS),SMALL(CODE(MAMES),ROW(1:1)),LARGE(PTS,ROW(1:1)))) ? - bad idea
Maybe this one:

Formula:

Please Login or Register  to view this content.

and Ctrl+Shift+Enter

if cells in col E (PTS) are not equels - sort col A (NAMES) by col E by LARGE().
If any cells in col E (PTS) are equels and cells in col F (HITS) are not equels - sort col A (NAMES) by col F (HITS) by LARGE().
If any cells in col E (PTS) are equels and any cells in col F (HITS) are equels - sort col A (NAMES) in alphabetical order

Updated

I forced to use LARGE()

**Tony Valko** · 12-20-2016, 07:36 PM

Try this...

I use a rank formula then sort based on rank.

Data Range

	B	C	D	E	F	G	H	I
4	NAMES	W	L	OTL	PTS	HITS	Rank	Leader
5	DANO	1	0	0	2	34	2	LORD
6	DELISLE	0	1	0	0	23	3	DANO
7	GOSSELIN	0	0	0	0	0	5	DELISLE
8	GRENIER	0	1	0	0	22	4	GRENIER
9	LEBEAU	0	0	0	0	0	6	GOSSELIN
10	LORD	1	0	0	2	54	1	LEBEAU
11	MALLETTE	0	0	0	0	0	7	MALLETTE
12	POIRIER	0	0	0	0	0	8	POIRIER
13	RIVARD	0	0	0	0	0	9	RIVARD
14	VACHON	0	0	0	0	0	10	VACHON

This formula entered in H5 and copied down:

=RANK(F5,F$5:F$14)+SUMPRODUCT(--(F$5:F$14=F5),--(G$5:G$14>G5))+SUMPRODUCT(--(F$5:F$14=F5),--(G$5:G$14=G5),--(B$5:B$14<B5))

This formula entered I5 and copied down:

=INDEX(B$5:B$14,MATCH(ROWS(I$5:I5),H$5:H$14,0))

LARGE with second/third array in case of a tie ?

LinkBack

Thread Tools

Rate This Thread

Display

LARGE with second/third array in case of a tie ?

Re: LARGE with second/third array in case of a tie ?

Re: LARGE with second/third array in case of a tie ?

Re: LARGE with second/third array in case of a tie ?

Re: LARGE with second/third array in case of a tie ?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Look within a large array of values for a small array that meets a certain criteria

[SOLVED] Select Case Array

Template or inspiration for create large scale business case / budget

[SOLVED] Select Case Statement Alternative Large Number of Combinations

Array case conversion

Transferring part of a large array to smaller array

Using an array in a case statment

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions