Show range of numbers based on thousands of records

Question

I have 15,000 numbers ranging between 1000-8000. Each number is assigned to one of six categories and subcategories, in some cases there are overlaps. Sample data:

value  cat1    cat2
2333   WHITE   A
2334   ORANGE  B
2335   ORANGE  A
2335   WHITE   B
2336   ORANGE  A
2336   WHITE   B
2336   RED     C
2337   RED     A
2338   RED     A
2339   RED     A
2340   RED     A

What's an efficient way of combining these values as such:

• White: 2333, 2335-2336
• Orange: 2334-2335, 2336
• Red: 2336-2340

Use case: Given a value such as 2338 A what is an efficient way of retrieving the cat1 value of RED?

For this use case, what would be a more efficient way of storing these strings to test against?

Language preference is JavaScript, PHP or MS Excel

Answer 1

To me it seems your looking in the wrong place for your solution.

The MOST efficient way to do the type of "retrieving" any variation/combination of your data is to "query" a database.

If it was me I'd dump all my data in MySQL and do simple query from there:

$num = 2338
$letter = A

SQL:

SELECT cat1
FROM dataSet
WHERE value = $num AND catb = $letter

Change your search values accordingly.

Answer 2

Because this question is tagged Excel, I will mention that you can use ADO and Jet to query Excel.