I have a spreadsheet which is like this: The sheet is mostly comprised
of groups (i.e. literally the cells says Group1, Group2, etc.). Each
group header is a named range. Underneath each group there maybe many
or no letters. I need to parse out each group cell along with its
letters, so I can then do a lookup on the letter rows for certain
data. The search should be able to flexible enough to find 1 group, 15
groups or many more groups. I will be doing this search function
programmatically, although if it makes sense to format the source
spreadsheet to help the search I can do that.

This is an example of the sheet, all on 1 column:

GROUP1 'named range
A 'data I need to the right of the letter
B
GROUP1 'if there are no letters then the blanks until the next group
are filled with the name of the group repeatedly.
GROUP1
GROUP1
GROUP2 'named range
C
D
GROUP2
GROUP3 'name range
GROUP3
GROUP3
GROUP4 'named range
GROUP4

If this makes sense, or if clarification is needed, please respond.

Thanks.