Last row in data using offset

[99moorem]

Hi Mr Excel,

I have been trying to figure this out for ages! and I know it is more less what I am trying to do but I can not get it work correctly.

There are two aims of this 1 to find the begining date and 2 to find the end date of data. Now let me be clear im not looking for the date's only the offset numbers of the start/end of the data. From that I can get to the date's quite easily.

I have the start of the data using
=MATCH(TRUE,INDEX($P11:$GT11>0,0),0)

This for the middle row and returns me a value of 8 which is exactly what I expect, this formula is in A11


The next part, finding the end of the data, I can not work out, this is as far as I got
=MATCH(TRUE,INDEX(OFFSET($P11:$GT11,0,A11)>0,0),0)

Sorry for the silly way this looks on my spreadsheet I have the dates aligned horizontally

Anyway below is an example of my data, There are columns of data to the left which have no blanks in them. On my sheet this data is an extract from P9:BN12 but my whole data goes up to GT45. There is only ever one line of data in each row

Im pretty sure this is just a formula and I am just being silly but any help would be appreciated

02/06/2014

09/06/2014

16/06/2014

23/06/2014

30/06/2014

07/07/2014

14/07/2014

21/07/2014

28/07/2014

04/08/2014

11/08/2014

18/08/2014

25/08/2014

01/09/2014

08/09/2014

15/09/2014

22/09/2014

29/09/2014

06/10/2014

13/10/2014

20/10/2014

27/10/2014

03/11/2014

10/11/2014

17/11/2014

24/11/2014

01/12/2014

08/12/2014

15/12/2014

22/12/2014

29/12/2014

05/01/2015

12/01/2015

19/01/2015

26/01/2015

02/02/2015

09/02/2015

16/02/2015

23/02/2015

02/03/2015

09/03/2015

16/03/2015

23/03/2015

30/03/2015

06/04/2015

13/04/2015

20/04/2015

27/04/2015

04/05/2015

11/05/2015

E

E

E

E

E

E

E

E

E

E

M

E

E

E

E

E

E

E

E

E

M

E

E

E

E

E

E

E

E

E

M

E

E

E

E

E

E

E

E

M

E

E

E

E

M

E

E

E

E

M

E

E

E

E

M

E

E

E

E

M

E

E

E

E

M

E

E

E

E

E

E

E

E

E

E

<TBODY>
</TBODY>

 

[skywriter]

This shows me the last date is in the 7th. column.

Excel 2010
	A	B	C	D	E	F	G	H
18		Friday August 1, 2014	Monday December 1, 2014	Saturday October 4, 2014	Tuesday April 1, 2014		Saturday March 1, 2014
19
20		7
Sheet3

Cell Formulas
Range	Formula
B20	=MATCH(9.99999999999999E+307,18:18)

 

[99moorem]

I think this formula works up until you find a blank cell, so for my example in row 3 I have blank rows that need to be accounted for.

I dont know this formula could be coupled with the offset formula? as I can find the first piece of information as a number

 

[skywriter]

I think this formula works up until you find a blank cell, so for my example in row 3 I have blank rows that need to be accounted for.

I dont know this formula could be coupled with the offset formula? as I can find the first piece of information as a number

A18 and F18 are blank in my example and it ignored them, what do you mean works up until you find a blank cell?

You said you have blank rows in row 3, did you mean to say you have blank cells?

 

[99moorem]

Hi,

Yes I did mean blank cells.

Sorry I think I read your example wrong.

I can see your example that it is missing the blanks and finding the last data, I tried to enter your data and formula exactly and seem to get a #N/A from the formula?

In B20 =MATCH(9.99999999999999E+307,18:18)

And the data is defiantly in rows 18.

I am using Excel 2010 currently (this is not my development platform or the end users so if this is the problem I will try it on Excel 2007 when I get home).

Thanks for your help and sorry for not reading your example correctly.

Marc

 

[99moorem]

Hi,

Could not get the match(9.9999E+307 to work correctly so instead I have built some vba to do this for me, didnt particulaly want to use vba for this but it may be the quickest solution for me (in terms of development time that is)

Sub Update_End_Of_TC_Data()
Dim Col As Long
Sheets("TC Forecast").Select
Col = Range("F9").End(xlToRight).Column
For Each cell In Range("A10:" & Range("F10000").End(xlUp).Offset(0, -5).Address)
    Start = 0
    For Each c In Range(cell.Offset(0, 17).Address & ":" & Col_Letter(Col) & cell.Row)
        If Start = 0 And c.Value = "E" Then
            cell.Value = c.Column - 1
        End If
        
        If c.Value = "E" Then
            Start = 1
        End If
        
        If Start = 1 And c.Value = "" Then
            cell.Offset(0, 1).Value = c.Column - 1
            Exit For
        End If
    Next
    If Start = 0 Then
        cell.Value = ""
    End If
Next
End Sub

Thanks for all your help.

Marc