=large question

[HeRoseInThree]

My formula =LARGE(AE$2:AE$999999,AK2) works great

BUT if it locates a duplicate value, it reports the first of the two twice. Is there a way to correct this?

Thanks in advance!

 

[ISY]

Hi

https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precision/

 

[Marcelo Branco]

See if this example helps

	A​	B​	C​
1​	Numbers​		List​
2​	12​		18​
3​	18​		17​
4​	18​		12​
5​	17​
6​	17​

Formula in C2 copied down
=IFERROR(LARGE(A$2:A$6,SUMPRODUCT(COUNTIF(C$1:C1,A$2:A$6))+1),"")

Remark
Do you really have 999999 rows with data?
For the sake of performance you should adjust the number of rows for your actual case - maybe AE$2 to AE$1000 (?)

M.

 

[HeRoseInThree]

No, I have 7521 rows now, and building fast. I made it a number large enough as not to have to have an issue later.

Secondary issue. Once the numbers are sorted, I need the names associated with the numbers. The formula I used, and works well as long as there are no duplicates, is =INDEX(AD$2:AD$83,MATCH(AL2,AE$2:AE$83,0))

If I have 2 guys with a score of 18 can it display both names with that score?

 

[Marcelo Branco]

An example

	A​	B​	C​	D​	E​	F​	G​
1​	Numbers​	Name​	List​	Name1​	Name2​	Name3​	Name4​
2​	12​	Mary​	18​	John​	Bob​
3​	18​	John​	17​	Richard​	William​
4​	18​	Bob​	12​	Mary​
5​	17​	Richard​
6​	17​	William​
7​

<tbody>
</tbody>

Formula in C2 copied down (see post 3)

Array formula in D2 copied across and down
=IF(COUNTIF($A$2:$A$6,$C2)>=COLUMNS($D2:D2),INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$C2,ROW($A$2:$A$6)-ROW($A$2)+1),COLUMNS($D2:D2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.
ps: not sure about the performance with data in thousands of rows...

 

[HeRoseInThree]

1. THANK YOU!!!!!!
2. To reduce my data, I used sum to add up several items and then used the formula that you provided.

=IF(COUNTIF($AE$2:$AE$83,$AL2)>=COLUMNS($AP2:AP2),INDEX($AD$2:$AD$83,SMALL(IF($AE$2:$AE$83=$AL2,ROW($AE$2:$AE$83)-ROW($AE$2)+1),COLUMNS($AP2:AP2))),"")

3. THANKS!!!!!!!!

 

[Marcelo Branco]

You are welcome. Glad to help

M.