Get row number corresponding to the first and last number in a series of repeating numbers (in a column)

Question

Level 1

5 points

Get row number corresponding to the first and last number in a series of repeating numbers (in a column)

Whew, that may have been a confusing title. Please don't be discouraged by my long post. It's mostly me just being thorough for you :D

I have a bunch of dates in a column. In column C is a function that simply returns the month number of the cell in column B from the same row. Now, I would like to use a function to tell me the position (row numbers) of the first and last number of a specified month. For example, I may only be interested in 3 (March). So I only want to know the row numbers of when March dates begin and end. How can I achieve this?

I initially tried using the MATCH function where the [matching-method] is set to [find largest value (1)] and [find smallest value (-1)] to tell me the first and last positions but this for some reason returned positions of different results that I wasn't even looking for (for example, saying a position for "2" when "2" wasn't even in the list- in other words, no dates for Feb were even in the list).

Alternatively, maybe someone can help me with my specific need. I am trying to find the first and last numbers (columns D and E in the image below) for a specified month. Each individual row has a specific date with a starting and ending number for that date. Therefore, I would like a function to tell me the first number from February, for example, and also the last number from February (just tell me the row numbers). This is where I tried using the match function I described above. Maybe I'm over thinking how to do this and don't need to use the month numbers column to achieve this?

Posted on Sep 1, 2022 11:49 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,780 points

Sep 1, 2022 5:28 PM in response to PuddleOfFat

Say Table 2::B2 has a number 1 through 12 and your formula is in Table 2,

=XMATCH(B2,Table 1::C,0,1) will do a top down search and give you the first position in column C that matches. It does not include the header(s) in this number.

=XMATCH(B2,Table 1::C,0,-1) will do a bottom up search and give you the last position in column C that matches. It does not include the header(s) in this number.

If you want the row number, the result from XMATCH will be off by how ever many header rows you have row. If you have one header row, add one to those two formulas to get the row number.

If you want the number from column D (and you have only one header row),use OFFSET:

=OFFSET(D1,=XMATCH(B2,Table 1::C,0,1)

And the number from column E

=OFFSET(E1,=XMATCH(B2,Table 1::C,0,-1)

You do not need the numbers in column C to do this. You could search on the month names in column A or, with the REGEX function as the search value, you can search on the actual dates in column B like this:

if Table 2::B2 has the month 1-12 and table 2::C2 has the year (as 2 digits just like in your Table 1)

=XMATCH(REGEX(B2&"/.*/"&C2),Table 1::B,2,1)

This gives the same results as the first function I posted except it also includes the year, if that is important to you.

Reply

Answer 2

Ralf-F

Level 4

2,741 points

Sep 1, 2022 2:49 PM in response to PuddleOfFat

Here my solution for the first task

I would go with XMATCH

Formula to find the first entry for the month = XMATCH(B$2,Table 1::A,0,1)+1

Formula to find the last entry for the month = XMATCH(B$2,Table 1::A,0,-1)+1

"+1" is needed because XMATCH will not count the header

For the second task I would use XLOOKUP

Formula to find the start for the month = XLOOKUP(B$2,Table 1::A,Table 1::B,"-",0,1)

Formula to find the end for the month = XLOOKUP(B$2,Table 1::A,Table 1::C,"-",0,-1)

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

Reply