Hi! I'm really sorry, my notifications were going into spam and I haven't been on here for a month or two!
Yes, this function just recursively perform a COUNTA() on columns of data. Due to the memory limitations you can't simply check every single column or row and recursively loop through that until something is found, so I start by checking every 10,000 columns (16000-16384[max col], 15000-15999 etc. then if something is found in that range, we then loop through the hundreds to pinpoint further (if we find something in 7000-7999, we then check 7900-7999, then 7800-7899 etc) and then every 10 and so on:
For last column of 1756:
COUNTA(16,000:16,384) = 0
COUNTA(15,000:15,384) = 0
......
COUNTA(1,000:1,999) > 0
COUNTA(1,900:1,999) = 0
COUNTA(1,800:1,899) = 0
COUNTA(1,700:1,799) > 0
COUNTA(1,790:1,799) = 0
COUNTA(1,780:1,789) = 0
COUNTA(1,770:1,779) = 0
COUNTA(1,760:1,769) = 0
COUNTA(1,750:1,759) > 0
COUNTA(1,759:1,759) = 0
COUNTA(1,758:1,758) = 0
COUNTA(1,757:1,757) = 0
COUNTA(1,756:1,756) = 1
We start by passing a zero to the column from, and the first row of the function will change that to 16,000 as the max number of columns is 16,384. This will always be the starting point of the function.
the variable i then calculates which multiple of 10 the recursively passed column from is in (up to max of 3 because it can't be greater than 1,000 (unlike rows, which are 1,000,000, so require to check 1;2;3;4;5). It does this by calculating the maximum value in the array where the remainder of the value divided by 10^the array is equal to 0. For example, if colFrom is 8000, then MOD(8000,10^3)=0 is true, whereas if I have 8100 then MOD(8100,10^3)=0 is false but MOD(8100,10^2)=0 is true and thus i = 2.
the variable j returns the log (default log10) of the colFrom
variable k returns only if j is an exact number (otherwise returns zero). This determines if we're "stepping down" because we've reached the last combination in our multiple of 10. For example, when we search between 1,000 and 1,999 and find nothing then we know that the step is now 100 (10^2) rather than checking 1,000 columns.
variable x is the counta function, from the colFrom value and colFrom plus 10^ variable i (the multiple of 10), subtracting 1 (e.g. if colFrom is 2000, then i will be 3 and so colTo will be 2000 + (10^3) -1 = 2999.
Finally we either return the result or pass the new last column back to the function until we find something. If we both find something (i.e. x=1) and i is also 0 (not divisible by 10^1;2;3) then we return that result. If i is not zero, then that just means we've found something within say the 1000 range (e.g. COUNTA(1000:19999)>0 = TRUE), so we'd subtract 100 from the colFrom value and pass that back recursively as the new starting point (e.g. 1900, 1800 and so on). Only when we checked every thousand, hundred and ten columns can we then check single columns for the first (or last!) entry.
Overall, it means that you shouldn't run out of memory when performing this function as it should only ever have a maximum of 43 iterations to find the last column using this method. I'm sure I could have done it differently, but this was the geekiest method I could both think of and also have my brain cope with.
