Sorting row by value (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote