1

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.

enter image description here

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 ROW and 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.

  • This can be solved using Named Range method!! – Rajesh S Jul 8 '19 at 8:15
  • Just to clarify, you're looking for a way to automatically create and maintain the range descriptions shown in col E based on the data in cols A:B, rather than how to use the col E ranges in a formula, right? – fixer1234 Jul 9 '19 at 16:38
  • @fixer1234, I will be incorporating them into formula, but have already found further material to let me insert it into a formula once I can have it displayed as per above. – Gnemlock Jul 9 '19 at 23:21
2

This uses a formula that contains some repetition, so I show it below with a couple of helper columns that will make it easier to follow. You can hide the helper columns, or incorporate their formulas into the column E results if you don't mind one that's long. This will automatically adjust for changes in the number of values.

enter image description here

I've added a placeholder (End) to mark the end of the data with an easy search target that's consistent with the way the formula works. You can hide that, or modify the formula to just look for the last row with a value in column B.

Using the helper columns, the formula in E2 is just:

="B" & G2 & ":B" & H2

Columns G and H are the first and last rows for the day in column D. The formula in G2:

=MATCH(D2,A:A,0)

This just finds the row of the column D day in the column A list. The formula in H2:

{=MATCH(FALSE,ISBLANK(OFFSET($A$1,G2,0,MATCH("End",A:A,0))),0)+G2-1}

This is an array formula. When you confirm it with Ctrl+Shift+Enter, Excel will add the curly braces.

This starts looking for the first non-blank cell after the day cell (i.e., the next day label; merged cells use the contents of the first cell in the range). "End" serves as the next "day label" for the last day. Then it adjusts for the location of the starting day label.

If you want to eliminate the helper columns, incorporating them into the column E formula would look like this for E2:

{="B" & MATCH(D3,A:A,0) & ":B" & MATCH(FALSE,ISBLANK(OFFSET($A$1,MATCH(D3,A:A,0),0,MATCH("End",A:A,0))),0)+MATCH(D3,A:A,0)-1}

Note that the column E formula becomes an array formula and needs to be confirmed with CSE.

Caveat: If the column A data doesn't start the column A contents, and somewhere above the list, the day names appear, these formulas will find that information first and won't work. In that case, use explicit range references. The end of the range can be an arbitrary cell below where the data might ever reach. The start of the range would be where the column heading "Day" is. To get correct row references, you will need to add adjustments to offset the locations locally within the lookup range for the starting row.

| improve this answer | |
1

I have a solution that almost works if this is how your Data Structure is ordered. This method get the range of all the Days except the last.

Formula: =CELL("address",OFFSET($A$1,MATCH(C2,$A$2:$A$16,0),))&":"&IFERROR(CELL("address",OFFSET($A$1,MATCH(C3,$A$2:$A$16,0)-1,)),"")

If you don't want the "$" symbols to be shown, you can use the SUBSTITUTE formula to remove those

Link to the image below:
Find Merged Cell Range - Sample Image

Edit:

If your Reference column (C:C in the image above) is continuous, you can use the below formula to get all the data: =CELL("address",OFFSET($A$1,MATCH(C3,$A$2:$A$16,0),))&":"&IFERROR(CELL("address",OFFSET($A$1,MATCH(C4,$A$2:$A$16,0)-1,)),ADDRESS(MATCH("zzz",A:A),1))

The IFERROR formula use ADDRESS(MATCH("zzz",A:A),1) to get the last filled row in Column A. I came up with this with the help of the answer in this post.

| improve this answer | |
  • I'm sorry, but I just don't see how this is useful if it can not find the values associated with the last day. – Gnemlock Jul 15 '19 at 3:31
  • I have edited the answer. Please check if that works – Gangula Jul 15 '19 at 16:18
  • I'm sorry, but I already have a working solution. – Gnemlock Jul 15 '19 at 22:43
0

The index of max and min per day can be achieved by using logical operators in an array formula. The range can be formulated by concatenating. I've attached my work to demonstrate.

enter image description here

This however isn't a particularly useful result in that the range is a string and doesn't tell you the numerical aspects of that array. A pivot table measuring count or max might be more worthwhile. If index is important, an alternative (without array formulas) would be to build a pivot table with min and max, then add / subtract to achieve starting and ending rows

| improve this answer | |
  • Your Max & Min evaluates Weekdays rather than values in Col B,, and OP has only one Weekday's Name in Merged Cells !! – Rajesh S Jul 8 '19 at 8:43
  • Merged / my structure are functionally equivalent. – Yaakov Bressler Jul 8 '19 at 13:46
  • 1
    I thank you for the time you took to try to answer my question; however, in practice, it simply does not work. I have a few parameters slightly different, so I recreated it as you have shown; but I still have a constant return of 0. There is every chance I am getting the formula wrong; you should put formula in plain text so it can be copy/pasted, instead of including it as an image. This would allow me to ensure I was not missing something really minor (though I'm pedantic, like that, so I do doubt it). – Gnemlock Jul 15 '19 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.