How many names have a duplicate in a date range
Results 1 to 10 of 10

Thread: How many names have a duplicate in a date range

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How many names have a duplicate in a date range

    Hi guys,

    Can someone please help with a formula?

    I have a list of names in column A, and some dates in column B. I need to work out how many names appear more than once between certain dates.

    E.g.
    I want to use the date range from 01 to 10 June. I have those dates in separate cells (e.g. start date in F1, End date in G1).
    My data would look like this:

    Col A Col B Col F Col G
    (Name) (Date) 01 June 2019 10 June 2019
    Carrie 31/May/2019
    Bob 01/June/2019
    Annie 02/June2019
    James 03/June/2019
    Carrie 04/June/2019
    Annie 05/June/2019
    Bob 05/June/2019
    John 05/June/2019
    Dave 06/June/2019
    Bob 07/July/2019

    (sorry, i tried to space the data out but extra spaces are being removed).

    Carrie appears more than once but one occurrence is before my date range so Carrie should not be counted.
    Bob and Annie appear more than once in my date range so I would need to return a count of 2. I don't need to know how many duplicates there are or which names appear more than once. I just need how many of the names in column A appear more than once in my date range.

    I would guess at this being combination of Countif and Sumproduct but I don't quite understand how to combine them.

    It would probably be easier in a pivot table but it needs to be a formula in this instance.


    Can anyone help?
    Last edited by Prinny; Jun 17th, 2019 at 02:31 PM.

  2. #2
    Board Regular
    Join Date
    Aug 2015
    Posts
    766
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How many names have a duplicate in a date range

    A
    B
    C
    D
    E
    F
    G
    1
    Name Date Start Date End Date
    2
    Carrie
    31-May-19
    Carrie
    1
    1-Jun-19
    10-Jun-19
    3
    Bob
    1-Jun-19
    Bob
    2
    4
    Annie
    2-Jun-19
    Annie
    2
    5
    James
    3-Jun-19
    James
    1
    6
    Carrie
    4-Jun-19
    John
    1
    7
    Annie
    5-Jun-19
    Dave
    1
    8
    Bob
    5-Jun-19
    9
    John
    5-Jun-19
    10
    Dave
    6-Jun-19
    11
    Bob
    7-Jul-19


    E2
    =IF($D2<>"",COUNTIFS($A$2:$A$11,$D2,$B$2:$B$11,">="&$F$2,$B$2:$B$11,"<="&$G$2),"") copy down



  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,714
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Try this array formula



    {=SUMPRODUCT((B2:B11>=F1)*(B2:B11<=G1)*(IF(COUNTIF(A2:A11,A2:A11)>1,1))*(MATCH(A2:A11, A2:A11,0)=ROW(B2:B11)-1))}


    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

  4. #4
    New Member
    Join Date
    Oct 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Quote Originally Posted by MARZIOTULLIO View Post


    E2
    =IF($D2<>"",COUNTIFS($A$2:$A$11,$D2,$B$2:$B$11,">="&$F$2,$B$2:$B$11,"<="&$G$2),"") copy down


    Thank you for your help, I'm sure this would work but I can't add the helper column to the data. Really appreciate you taking the time.

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Dante, thank you so much. Your array formula is exactly what I needed!

    This is way more advanced than my skill level, very impressive.

    I will need to do a lot of reading before it makes sense but it works perfectly. Thank you!
    Last edited by Prinny; Jun 17th, 2019 at 04:32 PM.

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

    Default Re: How many names have a duplicate in a date range

    Quote Originally Posted by Prinny View Post
    Dante, thank you so much. Your array formula is exactly what I needed!

    This is way more advanced than my skill level, very impressive.

    I will need to do a lot of reading before it makes sense but it works perfectly. Thank you!
    I'm glad to help you. I appreciate your kind comments.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Oct 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Sorry to ask again but I could use some more help with this. I've added the formula to my main data but I think I did something wrong as It's not giving the correct result.

    The main data is on a sheet named Tracking. Column H has the date and Column C has the names.

    My start date and end date are on sheet named Stats. This formula is also in a cell on the Stats sheet. Start date is cell E5. End date is cell G5.

    I adjusted the formula because my data starts on row 4 and not row 2. I just changed the end of it to -3 instead of -1 on Dante's original. I also added an IF to deal with blank cells in the range h4:h999.

    The formula is now:


    =SUMPRODUCT(IF(Tracking!H4:H999="",0,(Tracking!H4:H999>=E5)*(Tracking!H4:H999<=G5)*(IF(COUNTIF(Tracking!C4:C999, Tracking!C4:C999)>1,1))*(MATCH(Tracking!C4:C999,Tracking!C4:C999,0)=ROW(Tracking!H4:H999)-3)))

    I enter this as an array (ctrl+shift+enter).

    The problem I have is the formula is counting the names that fall in the date range but have a duplicate anywhere in the range. I need to count the names that fall in the date range and have a duplicate within the date range also.

    For context, the names are customers and the dates are order dates. I need to see how many customers ordered more than once in the range. For example, how many customers had more than one order in June.

    Did I break the formula?

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,714
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Quote Originally Posted by Prinny View Post
    Sorry to ask again but I could use some more help with this. I've added the formula to my main data but I think I did something wrong as It's not giving the correct result.

    The main data is on a sheet named Tracking. Column H has the date and Column C has the names.

    My start date and end date are on sheet named Stats. This formula is also in a cell on the Stats sheet. Start date is cell E5. End date is cell G5.

    I adjusted the formula because my data starts on row 4 and not row 2. I just changed the end of it to -3 instead of -1 on Dante's original. I also added an IF to deal with blank cells in the range h4:h999.

    The formula is now:


    =SUMPRODUCT(IF(Tracking!H4:H999="",0,(Tracking!H4:H999>=E5)*(Tracking!H4:H999<=G5)*(IF(COUNTIF(Tracking!C4:C999, Tracking!C4:C999)>1,1))*(MATCH(Tracking!C4:C999,Tracking!C4:C999,0)=ROW(Tracking!H4:H999)-3)))

    I enter this as an array (ctrl+shift+enter).

    The problem I have is the formula is counting the names that fall in the date range but have a duplicate anywhere in the range. I need to count the names that fall in the date range and have a duplicate within the date range also.

    For context, the names are customers and the dates are order dates. I need to see how many customers ordered more than once in the range. For example, how many customers had more than one order in June.

    Did I break the formula?
    Try this

    Code:
    {=SUMPRODUCT((IF(COUNTIFS(C4:C13,C4:C13,H4:H13,">="&E5,H4:H13,"<="&G5)>1,1))*(MATCH(C4:C13, C4:C13,0)=ROW(H4:H13)-3))}
    Regards Dante Amor

  9. #9
    New Member
    Join Date
    Oct 2015
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Fantastic! I added this and it works perfectly.
    I would never have worked this out on my own, I really can't thank you enough for all your help.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,714
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How many names have a duplicate in a date range

    Quote Originally Posted by Prinny View Post
    Fantastic! I added this and it works perfectly.
    I would never have worked this out on my own, I really can't thank you enough for all your help.
    Again with pleasure. Thanks for the feedback
    Regards Dante Amor

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
  •