Find a range of numbers based on first letter of adjacent cells

Hi all!

I have a list of vocabulary words in Excel. Currently I have all the words from A-Z in field B1 with their definitions in C1. In field A1, I have them numbered starting at 1. I want to section or group them off relative to their first letter in the alphabet. Though I do not want to physically move them from the whole group. I would like to use a function that will perform this action when called. I only need the lowest and highest numbers from the A1 field that relate to the words in B1, and from there I can probably figure out the rest..

I'm having trouble figuring out what function will give me the range of a specific section, lets say words that begin with the letter "W".
I need it to return the lowest and highest numbers from the "W" section using the numbers from A1. That way I can plug those numbers in my Random function which will then generate words from a specific group, rather than from the entire list

Here is a rough example if that helps:

____A______B________C____
1___1____Word1___Definition1
2___2____Word1___Definition1

I feel like most of this I can accomplish, I'm just stuck trying to figure out how to get the values that I will need to plug into the Rand function.

If someone can just suggest a function to use, I'm sure I can figure out the rest.

Thanks
Mike

Originally Posted by FlameRetired

Does the attached resemble what you want?

Thank you very much, yes that will work!

I didn't even think about using an array to solve it. I was focused on trying to find a function to make it work. Duh, still trying to learn this stuff.

Is there a better way in VB to plug my Min and Max values into my Rand UDF? Here is what I propose based on my limited knowledge (Notice the If Conditions are in pseudo-code for now):

If E15 = “A” then MinValue = J2 and MaxValue = J3
If E15 = ”B” then MinValue = K2 and MaxValue = K3

RandomNumber = Int((MaxValue - MinValue + 1) * Rnd) + MinValue

Is there a more efficient way to go through the alphabet than creating a IF Condition for each alphabet letter? Maybe similar to an array calculation, or can you perform array calculations in VB?

Thanks again!
Mike

Originally Posted by MarvinP

Hi Mike,

Perhaps a Pivot with Min and Max and a helper column would be useful? See the attached.

Thanks for the suggestion, but I wouldn't even know where to begin with using a Pivot table.

I'm still learning and my training material has not covered anything regarding Pivot tables -- yet that is.

Don't get me wrong, looking at the Pivot table looks like it could be helpful, but I wouldn't even know where to begin to make it work for me.

Thanks
Mike