HELP: Get top X values as column names not as values themselves ?

[Mr.Nob0OOdy]

I have one table with ten columns and each column has as a name the numbers from 1 to 10, in ascending order: 1,2,3,4,5,6,7,8,9,10.
On each row there are different values from 0 to 100+ which represents the numbers frequency, numbers been represented by each column name.

Job to do:
I want to get top X numbers which have the biggest values, for each row.
For example, the first row has:
1 2 3 4 5 6 7 8 9 10 <--- Column name (the numbers themselves).
8 0 4 9 7 6 6 1 5 6 <---values as frequency.
So, for this row I should get the following:
Top 3 numbers are: 4,5,1.
Which are, in fact, the column names and not the values from the rows but these column names are choose because they have the top 3 values from all this row, values been: 9,8 and 7.
Second, if you want top 4 numbers then we should get:
4,5,1,6,7,10. So, we get the column names as above because they have the top 3 values (9,7 and 8) + 4rd value which is 6 and on this row 6 is found on 3 cells, that is why we get 3 column names (numbers) instead: 6,7 and 10.

How to do this ?
Please help !

Thank you !

[NBVC]

You can do this in a 2-step process.

First assume your X input value is in K1.

In L1 enter formula:

Please Login or Register  to view this content.

copy across 10 columns and down.

These columns can be hidden.

Then in W4 enter formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across 10 columns and down.

[Mr.Nob0OOdy]

How to deal when you have more than 10 columns ?
I have a table with 60 columns and after a while it shows to me #NUM! after a while of dragging the formula in all the cells.
How to fix that ?

Thank you !

Found the fix: I did the other table on the other sheet. Now I put all the tables on the same sheet and it works.

[Mr.Nob0OOdy]

HELP:
It is not working to me.
Look at this attachment in order to see what I did.

http://www.crocko.com/608594BF452E47...0Testing_.xlsx

As you can see on "Last 2" sheet, if you set your input to 1 or 2 it shows to you more than 1 or 2 numbers. Why ? What's wrong with it ?

Thank you so much !

[Mr.Nob0OOdy]

None ? Please, guys, what's wrong with my file ? Why it shows more numbers ?

[Mr.Nob0OOdy]

BUMP The message you have entered is too short. Please lengthen your message to at least 10 characters.

[Mr.Nob0OOdy]

You can't download the file or what is the problem so I can fix it ?
Thanks !

[npamcpp]

Upload your file to the forum. Nobody wants to download from a site plastered with ads and wait times. Go Advanced > Paper Clip icon.

[Mr.Nob0OOdy]

File.xlsx

Look, I have uploaded it to the board as attachment.


As you can see on "Last 2" sheet, if you set your input to 1 or 2 it shows to you more than 1 or 2 numbers. Why ? What's wrong with it ?

Thank you so much !

[Mr.Nob0OOdy]

So, now could someone help me, please ?

Thank you so much !

[Mr.Nob0OOdy]

Any idea ? None ?

[Mr.Nob0OOdy]

BUMP The message you have entered is too short. Please lengthen your message to at least 10 characters.

[NBVC]

According to your original post:

Top 3 numbers are: 4,5,1.
Which are, in fact, the column names and not the values from the rows but these column names are choose because they have the top 3 values from all this row, values been: 9,8 and 7.
Second, if you want top 4 numbers then we should get:
4,5,1,6,7,10

So you say if you want top 4 you should get... 4,5,1,6,7,10. Isn't that more than 4 results? It's because one or more of the values that are smaller than 4th largest are repeated... same holds here. Your table only holds 0's, 1's and 2's ... so you are bound to get many repeats and therefore more than 1 or 2 results even if you enter 1 or 2.

[Mr.Nob0OOdy]

Ohh, you are right ! Sorry man !
Thank you very much !

[marked as solved now :D ]