1

Given a column of data here:

13
4
76
8

56
4

7
3

Is there a way to use MATCH() function (or similar) such that it will return the row number of the last blank or non-numeric row it finds? I will accept any solution that can search for either blank of non-numeric

=MATCH("", A:A) returns #N/A instead of 8

If not, is there a way to achieve this without using a script?

Usage: I'm essentially looking for a way to create a range from the last blank cell to the current row.

codemann8
  • 372
  • 7
  • 29
  • Possible duplicate of [Selecting the last value of a column](https://stackoverflow.com/questions/4169914/selecting-the-last-value-of-a-column) – TheMaster Sep 06 '18 at 11:47

1 Answers1

1

Solution

Last row number

=MAX(QUERY({ARRAYFORMULA(ROW($A:$A)),$A:$A},"select Col1 where Col2 is null"))

Range from current row to last empty row (in A column) - you may put this everywhere except col A:

=ADDRESS(ROW(),COLUMN(),4)&":"&ADDRESS(MAX(QUERY({ARRAYFORMULA(ROW($A:$A)),$A:$A},"select Col1 where Col2 is null")),COLUMN(),4)

Img

enter image description here

Is that what you where trying to get?

Grzegorz Mogilewski
  • 932
  • 6
  • 8
  • Not entirely, but I'll be looking into the methodology you used to retrofit if I can. What I'm trying to do is count duplicate values in the list since the last blank. So I'm trying to come up with a range that I can search the current row's value to see how many exist there. So the range you have on row 2 should be B1:B1, but on row 15 it would be B13:B14. Another potential snag is that I'm using INDIRECT as part of that range as the column letter is dynamic, so its usage inside an ARRAYFORMULA is limited. – codemann8 Sep 06 '18 at 19:22
  • Nevermind, the first formula works great! INDIRECT works because it's inside the ROW function. – codemann8 Sep 06 '18 at 19:37
  • Great, if you find it hard - let me know I try to help – Grzegorz Mogilewski Sep 06 '18 at 19:42