Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Fomrula for unique records between 2 dates

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fomrula for unique records between 2 dates

    Hi, I have a spreadsheet with the following type of information but is 2000 rows:

    Customer Date Lead Time
    A 01/01/2014 1
    A 04/01/2014 3
    B 05/01/2014 1
    C 04/02/2014 3
    A 05/02/2014 5

    I would like a formula to return number of customers including and between 1/1/14 and 31/1/14 so would expect the result 2 for the sample above.

    Also I would like return min lead time for the same time period so would expect this to return 1.

    Ive been racking my brain for days and trying lots of examples but keep failing so any help is greatly appreciated. Thanks.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    E1: 1-Jan-2014

    F1: 31-Jan-2014

    G1, control+shift+enter, not just enter:
    Code:
    =SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6>=$E1,
      IF($B$2:$B$6<=$F1,MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0)))),
      ROW($A$2:$A$6)-ROW($A$2)+1),1))
    
    H1, control+shift+enter, not just enter:
    Code:
    =IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
      IF($B$2:$B$6<=$F1,$C$2:$C$6)))),"")
    
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Nov 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Quote Originally Posted by Aladin Akyurek View Post
    E1: 1-Jan-2014

    F1: 31-Jan-2014

    G1, control+shift+enter, not just enter:
    Code:
    =SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6>=$E1,
      IF($B$2:$B$6<=$F1,MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0)))),
      ROW($A$2:$A$6)-ROW($A$2)+1),1))
    
    H1, control+shift+enter, not just enter:
    Code:
    =IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
      IF($B$2:$B$6<=$F1,$C$2:$C$6)))),"")
    
    Thank you very much these work perfectly well..

    On the second formula some return as blank where I have a couple of blank rows of data in the lead time column is it possible to get this to ignore blanks in the range $C$2:$C$6?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Quote Originally Posted by JimetyBob View Post
    Thank you very much these work perfectly well..
    You are welcome.

    On the second formula some return as blank where I have a couple of blank rows of data in the lead time column is it possible to get this to ignore blanks in the range $C$2:$C$6?
    I suppose you mean...
    Code:
    =IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
      IF($B$2:$B$6<=$F1,IF(ISNUMBER($C$2:$C$6),$C$2:$C$6))))),"")
    
    which must also be confirmed with control+shift+enter?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Nov 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Im still getting the formula returning a blank?? All you help is greatly appreciated..

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Quote Originally Posted by JimetyBob View Post
    Im still getting the formula returning a blank?? All you help is greatly appreciated..
    Did you confirm the formula with control+shift+enter?
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Yes I did use control+shift+enter, i forgot to mention some of the array for column B is also blank and are the same rows where column C is blank.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    Quote Originally Posted by JimetyBob View Post
    Yes I did use control+shift+enter, i forgot to mention some of the array for column B is also blank and are the same rows where column C is blank.
    Would you post a relevant small sample along with the lead time you want to see?
    Assuming too much and qualifying too much are two faces of the same problem.

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

    Default Re: Fomrula for unique records between 2 dates

    Quote Originally Posted by Aladin Akyurek View Post
    Would you post a relevant small sample along with the lead time you want to see?
    Avante Item Code Account Number Customer Date In Due Date Date Out Lead Time (Working Days) Turnaround Time (Working Days)
    270700.10078.032 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.61721.002 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.61721.004 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.62256.007 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.62256.002 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.62256.006 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    270700.62256.008 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    372100.11994.011 372100 B 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.61806.005 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.61806.006 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.61806.007 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.62061.003 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.62061.004 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.62061.005 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    320700.60156.003A 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0
    371000.61015.005 371000 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    371000.61016.007 371000 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    270700.10078.002D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    270700.10078.003D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    270700.10078.003.D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    270700.10078.002.D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    351800.61664.001.A 351800 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    372100.11994.011 372100 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    356600.62259.013 356600 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    365900.11601.015.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    365900.11067.010 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    365900.11027.013.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    365900.11601.016.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    372100.11971.022 372100 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    372100.11994.012 372100 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0
    223900.62180.001 223900 D 06-Jan-14 06-Jan-14 06-Jan-14 0 0
    372100.11984.036 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1
    372100.11971.023 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1
    372100.11853.033 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1

    The table is 12000 rows and has data added daily but the array i used was 2:12002 etc. Th elead time and turnaround have formulas in them so they are blank until columns d e f and have dates entered etc. then it puts the lead time etc.

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,055
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Fomrula for unique records between 2 dates

    What is the date interval for which the formula does not return what you expect? And, of course the value you want to see?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

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
  •