Last row in data using offset

9

99moorem

New Member
Joined
Jan 24, 2014
Messages
19
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
[TABLE="width: 1000"]
<TBODY>[TR]
[TD="class: xl65, width: 27, bgcolor: transparent"]02/06/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]09/06/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]16/06/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]23/06/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]30/06/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]07/07/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]14/07/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]21/07/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]28/07/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]04/08/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]11/08/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]18/08/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]25/08/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]01/09/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]08/09/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]15/09/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]22/09/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]29/09/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]06/10/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]13/10/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]20/10/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]27/10/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]03/11/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]10/11/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]17/11/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]24/11/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]01/12/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]08/12/2014
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]15/12/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]22/12/2014
[/TD]
[TD="class: xl67, width: 27, bgcolor: transparent"]29/12/2014
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]05/01/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]12/01/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]19/01/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]26/01/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]02/02/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]09/02/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]16/02/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]23/02/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]02/03/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]09/03/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]16/03/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]23/03/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]30/03/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]06/04/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]13/04/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]20/04/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]27/04/2015
[/TD]
[TD="class: xl65, width: 27, bgcolor: transparent"]04/05/2015
[/TD]
[TD="class: xl66, width: 27, bgcolor: #c5d9f1"]11/05/2015
[/TD]
[/TR]
[TR]
[TD="class: xl68"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl73"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl73"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]M
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl69"]E
[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
This shows me the last date is in the 7th. column.


Excel 2010
ABCDEFGH
18Friday August 1, 2014Monday December 1, 2014Saturday October 4, 2014Tuesday April 1, 2014Saturday March 1, 2014
19
207
Sheet3
Cell Formulas
RangeFormula
B20=MATCH(9.99999999999999E+307,18:18)
 
Upvote 0
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
 
Upvote 0
99moorem said:
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
Click to expand...

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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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)

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
535
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
275
Raj1979
R
SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
167
SFCChase
SFCChase
S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
215
sanrama
S
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K

Forum statistics

Threads
1,224,884
Messages
6,181,555
Members
453,053
Latest member
Kiranm13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top