Thanks:  0
Likes:  0

# Thread: Want to equate a number with text bit confusing

1. This is what i need to do ---- I have a row which i haved search to find the highest number in. Now that i have found that number i dont want it printed in this cell i want the name of that row printed in the cell, ie the product name. i.e. what i have is a row of products with a row of numbers underneath i want the 6 products with the highest number to be displayed on a sepearate colume, ie the most important ones.

2. Supposing the product names are in cells A1:Z1 and the corresponding values are in A2:Z2.

Supposing also that you have the numbers 1 to 6 in cells A4:A9, and that you want the corresponding "top 6" product in cells B4:B9.

Fill in the following formula in B4:

=INDEX(\$A\$1:\$Z\$1;MATCH(LARGE(\$A\$2:\$Z\$2;\$A4);\$A\$2:\$Z\$2;0))

And copy down through B9.

There is one drawback to this formula: it only works when the scores of your products are all different. It two products have the same score, one of these will be displayed twice in the top 6 list...

Marc

3. On 2002-03-20 04:02, drag-driver wrote:
This is what i need to do ---- I have a row which i haved search to find the highest number in. Now that i have found that number i dont want it printed in this cell i want the name of that row printed in the cell, ie the product name. i.e. what i have is a row of products with a row of numbers underneath i want the 6 products with the highest number to be displayed on a sepearate colume, ie the most important ones.
A new thread? OK.

Consider

{"p1","p2","p3","p4","p5","p6","p7","p8","p9","p10";
1,8,"","","",3,"","",0,8}

in A1:J2, where "" stands for a blank cell.

In A3 enter and copy across as far as needed:

=IF(ISNUMBER(A2),RANK(A2,\$A\$2:\$J\$2)+COUNTIF(\$A\$2:A2,A2)-1,"")

In A4 enter and copy down as far as needed:

=IF(COUNT(\$A\$2:\$J\$2)>ROW()-ROW(\$3:\$3),INDEX(\$A\$1:\$J\$1,MATCH(ROW()-ROW(\$3:\$3),\$A\$3:\$J\$3,0)),"")

See for what ROW() and ROW(\$3:\$3) do my reply to your "largest 6" topic.

4. That works but i cant seem to copy the second formulae to a different referance and have it work other than th A4 position, im sure their is something simple in the formulae to change but i cant see what it is.

5. Realy need to be able to move my table which contains the top 6 having it under the final row does not work.Any help much apreciated

6. On 2002-03-20 06:54, drag-driver wrote:
Realy need to be able to move my table which contains the top 6 having it under the final row does not work.Any help much apreciated
Not knowing the lay-out of your data, it's hard for me to suggest to adapt the suggested formulas to your lay-out. Maybe you could send me a copy of your WB along with the location where you want the top 6 product names.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•