Group last name based on range in alphabet

[jharding]

I have a project where I need to group by first letter of last name. The groups can be dynamic and would range from letters in the alphabet (ie. A-E, F-J, K-P, Q,T,W-Z)

The start and end of each of these ranges is in a cell so I can read the cells and create the groups in variables if necessary. Since it is dynamic, I have row header "Folder Group 1, 2, 3, etc." so I can read each row that begins with "Folder Group*" and create a variable for this (should I use an array to store this?

I plan on converting the letters to numbers so I can easily calculate which range the first letter of the last name is in. I can create an alphabet array and then select the indexes, or I found to use Code(Upper(letter))-64 and that will get me the alphabet position

Does this seem like the best way to do it? I plan on creating public variables with the ranges so each last name I run through a function can reference the ranges. Just wondering if storing the ranges as arrays makes the most sense, since I need two variables per range

Also, since the number of groups is dynamic (could be 5, could be 10, could be 2), should I use one global array for definition?

 

[Kaper]

The question is a bit general, but let me try:

I'd declare public dynamic array of strings so in general module at the top (below Options etc)

Public groups() As String

And then before first use assign grouping like

Dim i As Long
i = 5 'rour real number of groups
ReDim groups(1 To i)
groups(1) = "ABCDE"
groups(2) = "FGH"
groups(3) = "IJKLMN"
' and so on

When your groups number changes you can redim and reassign again
then finding if a name is in group x would be checikng
instr(groups(x),left(the_name,1))>0
but then checking in which group is letter R would require looping throu all groups from top to the one containing the letter (and exiting).

The second idea and probably much better:

So may be the reference two columns table with all letters and groups will be better for you
A 1
B 1
C 1
D 2
E 2
F 2
G 2
and so on.

This reference could be in a table, but probably much better suited data structure would be a dictionary.
If you are not familiar with directories, I think this is reasonably written material: Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

 

[Peter_SSs]

Your question has had 40 "views" and no responses so I have three suggestions:
(Edit: Well, One response as I typed. )

1. Update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
   
2. Give a smallish set of varied sample data and the expected results.
   MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
   
   Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
   
   
3. Tell us whether you are looking for a particular type of solution (eg, Formula or macro or power query)