Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting row by value (Excel 97)

    I have a worksheet that includes a column of values from 1 to 500. I need to map the 500 different values down to 7 distinct categories. The values appear to be randomly assigned (in other words: the values that map to a single category are not continuous). Is there an easier way to do this other than nesting 500 "IF" statements? (as if that were possible!)

    I am using Excel 97. To my knowledge, I do not have any SRs installed.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting row by value (Excel 97)

    Can you give a better description of how the 500 values map to the 7 categories? Are there distinct ranges of values that map to categories?
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting row by value (Excel 97)

    If you have a list of the 500 values and their associated categories in a table, then LOOKUP will work.

    So if you have the values 1 to 500 in cells A1:A500 and the categories in B1:B500 then to find the category for the value in D1 you would use the formula
    =LOOKUP(D1,$A$1:$A$500,$B$1:$B$500)

    Hope this is useful.

    Ian.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting row by value (Excel 97)

    The 500 values appear to almost be random. For example, the following values map the category number 7: 1-9, 12-35, 45, 65, 214-215, 243, 484, 496-500. None of the other categories have any more of a pattern to it.

    Currently, I do not have the values in electronic format. I may just have to manually create a LOOKUP table with all 500 values, but I was hoping to find a less entry-intensive solution. Is it possible to include a range of numbers within a single lookup row? If so, how would that work. Thanks.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting row by value (Excel 97)

    Thanks for your reply. I already thought of using a LOOKUP table, but I don't aleady have the values in electronic format. A lookup table will work well, but I was hoping to find a less entry-intensive solution. Probably no such animal exists. Is it possible to include a range of values in a single lookup row?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting row by value (Excel 97)

    If that is the case, then you will have to create a table of values and use one of the lookup functions. You can create a table with ranges of numbers, that is what the fourth parameter to VLOOKUP is used for. There is a fairly good example of doing that in the help files.
    Legare Coleman

Posting Permissions

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