Want to equate a number with text bit confusing

drag-driver

Board Regular
Joined
Feb 18, 2002
Messages
91
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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.

Aladin
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top