Excel Formula to Count Rows Based on First and Last Occurrence of a Specific Text within a Series of Strings.

[djmyers]

I am trying to write a formula that will count the number of rows between the first appearance of a specific text within a string, and the last appearance of that same text.

For example, if Column A contains the following values...

Email sent 12/15/2017.
Email sent 12/15/2017.
Sold in 2017.
Last customer contact on 11/21/17.
Last customer contact on 11/27/17.
Email sent 12/15/2017.
Email sent 12/15/2017.
Email sent 12/15/2017.
Email sent 12/15/2017.
Email sent 12/15/2017.
Last customer contact on 11/20/17.

...I would like a count of the rows beginning with the first appearance of 12/15/2017, and the last appearance of this same value. (In the example shown above, the result would be 10.)

 

[Aladin Akyurek]

Try...

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("12/15/2017",A1:A11),ROW(A1:A11)-ROW(A1)+1)-MATCH("*"&"12/15/2017"&"*",A1:A11,0)+1,"not found")

 

[djmyers]

This works beautifully! Thank you, Aladin!

 

[Aladin Akyurek]

This works beautifully! Thank you, Aladin!

You are welcome.

 

[djmyers]

Aladin:

Before I asked this question here, I asked it at Stack Overflow. There, user ashleedawg asked about the inclusion of the 9.99999999999999E+307 reference within the formula. I have tried to figure that out, but feel that I don't understand the structure of the formula you provided well enough to answer ashleedawg's question. When I attempted the formula without including that portion, however, I discovered it didn't work properly for my purposes.

When you have a moment, would you mind walking me through how this formula works?

Thank you!

 

[Aladin Akyurek]

Aladin:

Before I asked this question here, I asked it at Stack Overflow. There, user ashleedawg asked about the inclusion of the 9.99999999999999E+307 reference within the formula. I have tried to figure that out, but feel that I don't understand the structure of the formula you provided well enough to answer ashleedawg's question. When I attempted the formula without including that portion, however, I discovered it didn't work properly for my purposes.

When you have a moment, would you mind walking me through how this formula works?

Thank you!

The bit

LOOKUP(9.99999999999999E+307,SEARCH("12/15/2017",A1:A11),ROW(A1:A11)-ROW(A1)+1)

is a regular LOOKUP, i.e.

1) LOOKUP(BigNum,NumericArray1,NumericArray2)

​

2) NumericArray1 =

SEARCH("12/15/2017",A1:A11)​

for SEARCH returns whole numbers which indicate positions of the text date substring in the strings of A1:A11 or #VALUE ! (failure).

3) NumericArray2 =

ROW(A1:A11)-ROW(A1)+1​

which consists of the row number of each cell of A1:A11, such that we get 1, 2, ...,N where N is the size of A1:A11.

4) LOOKUP with BigNum correlates (a) the last whole number from NumericArray1 with (b) a number from NumericArray2, that is, the position (a) and the position (b) are the same.


5) The number from NumericArray2 is exactly the intermediate result we need to get the final result.

See also:
https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html#post3568323
https://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

Hope this helps.

 

[djmyers]

Thank you for your outstanding explanation, Aladin!

 

[Aladin Akyurek]

Thank you for your outstanding explanation, Aladin!

You are very welcome.