Need help with a formula

[Greg Simoni]

Would appreciate some help with a formula in Excel 2010.
On the attached file I get the top 5 counts on a row on COL AH5 down. This works ok.
I need on COL AI the value for each of the top 5 counts which are from C4:AF30.
I already have filled two rows for better illustration.
Thank you so much.

 

[Hui]

Greg

Firstly, Welcome to the Chandoo.org Forums

AI5: =INDEX($C$4:$AG$4,MATCH(LARGE(C5:AG5,1),C5:AG5,0))& ", "&INDEX($C$4:$AG$4,MATCH(LARGE(C5:AG5,2),C5:AG5,0))& ", "&INDEX($C$4:$AG$4,MATCH(LARGE(C5:AG5,3),C5:AG5,0))& ", "&INDEX($C$4:$AG$4,MATCH(LARGE(C5:AG5,4),C5:AG5,0))& ", "&INDEX($C$4:$AG$4,MATCH(LARGE(C5:AG5,5),C5:AG5,0))

Copy down

 

[bosco_yip]

Concatenate Top 5 value Column Number (p.s. the top 5 value has duplicate)

In AI5, array formula copy down :

=SMALL(IF(C5:AF5=LARGE(C5:AF5,1),COLUMN(A5:AD5)),SUM(--(LARGE(C5:AF5,1)=LARGE(C5:AF5,1))))& ", "&SMALL(IF(C5:AF5=LARGE(C5:AF5,2),COLUMN(A5:AD5)),SUM(--(LARGE(C5:AF5,{1,2})=LARGE(C5:AF5,2))))& ", "&SMALL(IF(C5:AF5=LARGE(C5:AF5,3),COLUMN(A5:AD5)),SUM(--(LARGE(C5:AF5,{1,2,3})=LARGE(C5:AF5,3))))& ", "&SMALL(IF(C5:AF5=LARGE(C5:AF5,4),COLUMN(A5:AD5)),SUM(--(LARGE(C5:AF5,{1,2,3,4})=LARGE(C5:AF5,4))))& ", "&SMALL(IF(C5:AF5=LARGE(C5:AF5,5),COLUMN(A5:AD5)),SUM(--(LARGE(C5:AF5,{1,2,3,4,5})=LARGE(C5:AF5,5))))

p.s. Array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together in stead of just ENTER

Regards
Bosco

 

[Greg Simoni]

@Bosco Thank you! Your formula works perfectly and yes, there are duplicates counts, however, you did it right because the same count may be shared for more than one number. Example on row 6 where the count 110 is share by two numbers.
@Hui I liked your formula but like I said above there are duplicates counts for separate numbers and what I'm pulling are the numbers so... Thank you!
Glad to get help here from nice guys!

 

[John Jairo V]

Hi, to all!

Another option attached (CSE). Blessings!