I have a set of data, and I need to run some basic analytics on it. However, I need to process the data by day of the week, which is indicated in merged cells for a series of associated cell ranges (see image below).
I have a manual process for this, but it is a bit cumbersome; I want to create a table of the cell ranges by day, based on the merged cells. These ranges will get used in other formulas to do what I need. The number of data rows for a given day can change, so I need the range table to automatically reflect the data.
The above image captures what I am looking for at this stage; five days have been listed in column A, with a varying count of associated numbers in column B. The range table shows the ranges needed for the sets of values. For example, "Friday" has 2 values, and ranges from B16:B17. The range lists "B16:B17".
If I was to add two additional numbers, and extend the merged "Friday" cell to include the next two cells, this listed range should automatically change to "B16:B19".
I have been looking around on the Internet for such a feature, but I am getting way too many hits for unrelated tasks, such as finding merged cells or deleting them. I tried using the
ROWS formula, but they do not seem to give me the results I am looking for.
In case it matters, I am using the Office 365 version of Excel.