+ Reply to Thread
Results 1 to 16 of 16

Obtaining the actual Row number from a date in column A

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Obtaining the actual Row number from a date in column A

    I am not sure how to obtain the row number from the first date (selected by reference to a separate report sheet) in a column of dates and the row number of the last date (also selected in the seperate report sheet) also in the same column of dates.

    e.g.

    Col A = Dates

    R1 01/04/11
    R2 01/04/11
    R3 01/05/11
    R4 01/06/11
    R5 30/06/11
    R6 30/06/11

    So, if I needed the Row number of 01/04/11 and 30/06/11 it would need to be Rows R1 and R6, not R2 and R5

    Can anyone help please?
    Gary Lockton

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Obtaining the actual Row number from a date in column A

    vLookup can do that for you (see the example).
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,292

    Re: Obtaining the actual Row number from a date in column A

    Pl see attached file.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Obtaining the actual Row number from a date in column A

    delete reply (reply on the wrong topic).
    Last edited by oeldere; 08-11-2012 at 10:42 AM.

  5. #5
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    Thanks kvsrinivasamurthy

    How would I select the say opening date at (C1) and closing date at (C2) and get the row numbers, so if I changed these dates the row numbers would change accordingly.

  6. #6
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    oeldere

    You deleted a response, yet an email from the forum stated you asked a question which is not here, can you re post?

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Obtaining the actual Row number from a date in column A

    Please reply on my answer in #2.

  8. #8
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    oeldere

    Sorry, my response is missing.

    Your example returned row 1 and 5 when it should be row 1 and 6 in the example, also what if the dates changed in the report sheet to select a different date range?
    I do not understand enough about whether MATCH could be used?

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Obtaining the actual Row number from a date in column A

    The solution in #3 is also an very good option.

  10. #10
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Obtaining the actual Row number from a date in column A

    here are a few different ways to derive the information you are looking for. see if they work for you.

    i have reused @kvsrinivasamurthy's file and highlighted the new approaches with different colour.
    Attached Files Attached Files
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  11. #11
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Obtaining the actual Row number from a date in column A

    Try the two formulas
    =MATCH(MIN(A:A), A:A, 0)

    and (if the dates are sorted ascending)
    =MATCH(MAX(A:A), A:A)
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  12. #12
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    Hi icestationzebra

    Many thanks for your quick response.

    I have entered your formulas into my live workbook but it has highlighted an error that I hadn't thought of.

    If the end date in I4 does not match any date in Col A then it returns an #N/A error, is there any way of the formula looking at the next earliest date and returning its row number?

    Many thanks

  13. #13
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    Hi mikerickson

    Having looked at your formula I don't see where it checks for the start and end date as discussed in my earlier thread.

    I assume your formulas find the earliest entry and the last entry?

    Many thanks

  14. #14
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Obtaining the actual Row number from a date in column A

    something like this?
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-27-2012
    Location
    Grantham, England
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Obtaining the actual Row number from a date in column A

    Thank you icestationzebra that is just what I wanted

    Many thanks

  16. #16
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,204

    Re: Obtaining the actual Row number from a date in column A

    Did the formulas I gave you work?
    They assume that MIN(A:A) is the start date and MAX(A:A) is the ending date.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1