=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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Numbers​

[/td][td][/td][td]

List​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

12​

[/td][td][/td][td]

18​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

18​

[/td][td][/td][td]

17​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

18​

[/td][td][/td][td]

12​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

17​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

17​

[/td][td][/td][td][/td][/tr]
[/table]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Numbers​

[/TD]
[TD]

Name​

[/TD]
[TD]

List​

[/TD]
[TD]

Name1​

[/TD]
[TD]

Name2​

[/TD]
[TD]

Name3​

[/TD]
[TD]

Name4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

12​

[/TD]
[TD]

Mary​

[/TD]
[TD]

18​

[/TD]
[TD]

John​

[/TD]
[TD]

Bob​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

18​

[/TD]
[TD]

John​

[/TD]
[TD]

17​

[/TD]
[TD]

Richard​

[/TD]
[TD]

William​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

18​

[/TD]
[TD]

Bob​

[/TD]
[TD]

12​

[/TD]
[TD]

Mary​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

17​

[/TD]
[TD]

Richard​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

17​

[/TD]
[TD]

William​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.