Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Want to equate a number with text bit confusing

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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