Finding unique data ranges

Ive been trying to figure out how to deal with this problem and im curious if anyone has found a good way to deal with it

I have a number of options assigned to number range and im trying to figure out all the possible unique combination

for example
option a is assigned to widget 1-100
option b is assigned to widget 5-10
option c is assigned to widget 6 and 22
option d is assigned to widget 9-100
...etc

I want to figure out how many unique widgets i have what options all each unique widget has. I cant seem to figure out a good way to build a data structure to deal with this though. Has anyone encountered a similar problem and come up with any good ways to solve it

You said. "I want to figure out how many unique widgets i have what options all each unique widget has"
---------------
Not clear. Can you restate this? It's the crux of the problem.

so in this case i would have
1-4 A
6 ABC
5, 7, 8 AB
9-10 ABD
11-21 and 23-100 AD
22 ACD

So 6 unique widget sets and the known ranges. I want to then display the unique ranges to the user and in the form of a list and they can find out what options each unique set has

How many options are there? Just 4?

Number of options is variable but a couple hundred

I noticed in your second example in post #3 that the numbers were unique.
That is, you can have hundreds of numbers but no number repeats.
Is this supposition correct?

I believe that would be a correct assumption although the numbers are not always in series so its possible to have 1-50 an then 60-100 with no valid numbers in the 50-60 range

No, that'd be fine. Then I would tackle your problem from there. Possibly an array

aRange(x,y)

You would store the numbers in x, ( and use the min and max here'd be perfect). You could then use an array of option buttons ( just a suggestion), and using select case you could modify the second half of the array, adding the widgets selected. Then using a simple for next loop process all the array,and correleate the data as needed.

I think? ;)

Pete i dont really understand what you are suggesting so maybe i wasnt real clear on explaining the problem

I have a huge worksheet of options and in one of the cells in the row there is an identifier on which production runs had this option and which ones didn't. Therefore I'm looking to find what the unique variants are.

The slow way to do this would be to create an array that encompass all the models so say i have a 10000 production run units. I would have an array[10000] of strings and i would run through all the options and add them to the affected model. Then i would go through every block in the array and assign production run numbers to every unique set of options.

There should be a significantly more efficient way of doing this with trees and recursion but i cant quite figure it out so i was hoping to find some suggestions.

Ok, lets see if I am getting this right

1 2 3 4 5 6 7 8 9 10
a ab a abc a a b b a dc

Is an example sheet

output would be

1 - 6 a
2,4,7,8 b
4,10 c
10 d

Is this something of what you are looking for?

Yeah that would be right

Hmmm. I am not sure if there is a more efficient way. You are describing a perfect problem for using arrays. With 10000 different pieces you will run into wait times regardless (this is where a progress bar would come into play). I wouldn't use a collection as it's too big. You could certainly create a function and submit each cell to it which would certainly reduce your coding. I admittedly am an amateur coder so possible one of the experts here may have a better idea, but to me you will just have to doze your way through the data. I am addicted to arrays anyway.

I think its possible if you dump the info into a table then do a pivot on the data. Check out Access documentation, it supports pivots if I am not mistaken.