Results 1 to 9 of 9

Thread: Find names based on date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2018
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find names based on date

    Hello,


    I'm looking for 2 formulas or a macros that do the following. The first formula; In column D1 will give a date based on the data corresponding in the cell range if the name corresponds in column B and is within columns A1:A6. I'm not sure how to say find the date in column A and grab the data from corresponding cells in Column B however only up to the next date in column A.

    The second formula is need is to place names in column E based on the date in column D

    a b c d e f g h
    1 8/4/2019 John
    2
    3 jacob
    4
    5 Jill
    6 john
    7 8/5/2019
    8 john
    9 jacob
    10
    11 jill
    12
    13 8/6/2019 bill
    14 joe
    15
    16 8/7/2019
    17 john

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find names based on date

    You need something like this:

     ABCDE
    108/04/2019John 08/04/2019John
    2    jacob
    3 jacob  Jill
    4    john
    5 Jill 08/05/2019john
    6 john  jacob
    708/05/2019   jill
    8 john 08/06/2019bill
    9 jacob  joe
    10   08/07/2019john
    11 jill   
    12     
    1308/06/2019bill   
    14 joe   
    15     
    1608/07/2019    
    17 john   




    Are there really blank cells between dates and between names?
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find names based on date

    Quote Originally Posted by DanteAmor View Post
    You need something like this:

    A B C D E
    1 08/04/2019 John 08/04/2019 John
    2 jacob
    3 jacob Jill
    4 john
    5 Jill 08/05/2019 john
    6 john jacob
    7 08/05/2019 jill
    8 john 08/06/2019 bill
    9 jacob joe
    10 08/07/2019 john
    11 jill
    12
    13 08/06/2019 bill
    14 joe
    15
    16 08/07/2019
    17 john




    Are there really blank cells between dates and between names?
    In column B there are blanks however in Column D, D2 should have a date.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find names based on date

    You can put the desired result
    Regards Dante Amor

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Find names based on date

    Still not very clear to me, but see if this is it.
    If not, as Dante has requested, please post the table from post 1 again but also include in the table the exact results you want in columns D & E.

    Try copying these formulas down as far as you might need.

    Find Names Based On Date

    ABCDE
    1DateName DateName
    28/04/2019John 8/04/2019John
    3 8/04/2019jacob
    4 jacob 8/04/2019Jill
    5 8/04/2019john
    6 Jill 8/05/2019john
    7 john 8/05/2019jacob
    88/05/2019 8/05/2019jill
    9 john 8/06/2019bill
    10 jacob 8/06/2019joe
    11 8/07/2019john
    12 jill
    13
    148/06/2019bill
    15 joe
    16
    178/07/2019
    18 john
    19
    20

    Spreadsheet Formulas
    CellFormula
    D2=IF(E2="","",LOOKUP(9.99E+307,A$2:INDEX(A$2:A$20,AGGREGATE(15,6,(ROW(A$2:A$20)-ROW($A$2)+1)/(B$2:B$20=E2),COUNTIF(E$2:E2,E2)))))
    E2=IFERROR(INDEX(B$2:B$20,AGGREGATE(15,6,(ROW(B$2:B$20)-ROW(B$2)+1)/(B$2:B$20<>""),ROWS(E$2:E2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4



    BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular
    Join Date
    Dec 2018
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find names based on date

    A B C D E
    1 Date Name Date Name
    2 8/04/2019 John 8/04/2019 John
    3 8/04/2019 jacob
    4 jacob 8/04/2019 Jill
    5 8/04/2019 john
    6 Jill 8/05/2019 john
    7 john 8/05/2019 jacob
    8 8/05/2019 8/05/2019 jill
    9 john 8/06/2019 bill
    10 jacob 8/06/2019 joe
    11 8/07/2019 john
    12 jill
    13
    14 8/06/2019 bill
    15 joe
    16
    17 8/07/2019
    18 john
    19
    20




    The formula to generates names works however the formula to generate dates didn't work, or maybe i didn't correctly change it to my needs. The way Column D and E was posted was correct. I have changed the indicated columns however it didnt work.

    =IF(N10="","",LOOKUP(9.99E+307,A$10:INDEX(A$10:A$31008,AGGREGATE(15,6,(ROW(A$10:A$31008)-ROW($A$10)+1)/(B$10:B$31008=N10),COUNTIF(N$10:N10,N10)))))
    Last edited by Sparda142; Aug 27th, 2019 at 02:36 PM.

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Find names based on date

    Try this array formulas:

     ABCDE
    1DateName DateName
    208/04/2019John 08/04/2019John
    3   08/04/2019Jacob
    4 Jacob 08/04/2019Jill
    5   08/04/2019john
    6 Jill 08/05/2019john
    7 john 08/05/2019jacob
    808/05/2019  08/05/2019jill
    9 john 08/06/2019bill
    10 jacob 08/06/2019joe
    11   08/07/2019john
    12 jill   
    13     
    1408/06/2019bill   
    15 joe   
    16     
    1708/07/2019    
    18 john   
    19     
    20     

    CellArray Formula
    D2{=INDEX($A$1:$A$99,LARGE(IF($A$2:$A$99<>"",IF(ROW($A$2:$A$99)<=SMALL(IF($B$2:$B$99=E2,ROW($B$2:$B$99)),COUNTIF($E$2:E2,E 2)),ROW($A$2:$A$99))),1))}
    E2{=IFERROR(INDEX($B$1:$B$99,SMALL(IF($B$2:$B$99<>"",ROW($B$2:$B$99)),ROWS($G$2:G2))),"")}



    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

  8. #8
    Board Regular Sam_D_Ben's Avatar
    Join Date
    Oct 2012
    Location
    New Jersey, USA
    Posts
    371
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find names based on date

    Solutions like bullet shots
    Sam_D_Ben

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Find names based on date

    Quote Originally Posted by Sparda142 View Post
    The formula to generates names works however the formula to generate dates didn't work, or maybe i didn't correctly change it to my needs. The way Column D and E was posted was correct. I have changed the indicated columns however it didnt work.

    =IF(N10="","",LOOKUP(9.99E+307,A$10:INDEX(A$10:A$31008,AGGREGATE(15,6,(ROW(A$10:A$31008)-ROW($A$10)+1)/(B$10:B$31008=N10),COUNTIF(N$10:N10,N10)))))
    You appear to have adapted the formula correctly.
    Here it is working for me.

    When a forum suggestion does not work for you, it doesn't help just saying it "didn't work". That gives us nothing to go on to try to resolve the problem. ;0
    You need to say in what way it didn't work, For example
    - It gave an error message (what message)
    - It returned the incorrect result.(what result did it return for what sample data and what result should it have returned
    - It crashed Excel
    etc

    Find Names Based On Date

    ABMN
    9DateNameDateName
    108/04/2019John8/04/2019John
    11 8/04/2019jacob
    12 jacob8/04/2019Jill
    13 8/04/2019john
    14 Jill8/05/2019john
    15 john8/05/2019jacob
    168/05/2019 8/05/2019jill
    17 john8/06/2019bill
    18 jacob8/06/2019joe
    19 8/07/2019john
    20 jill
    21
    228/06/2019bill
    23 joe
    24
    258/07/2019
    26 john
    27

    Spreadsheet Formulas
    CellFormula
    M10=IF(N10="","",LOOKUP(9.99E+307,A$10:INDEX(A$10:A$31008,AGGREGATE(15,6,(ROW(A$10:A$31008)-ROW($A$10)+1)/(B$10:B$31008=N10),COUNTIF(N$10:N10,N10)))))
    N10=IFERROR(INDEX(B$10:B$31008,AGGREGATE(15,6,(ROW(B$10:B$31008)-ROW(B$10)+1)/(B$10:B$31008<>""),ROWS(N$10:N10))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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