Delivery Drop No.?

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

Thread: Delivery Drop No.?

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delivery Drop No.?

     
    I am currently developing a route check sheet for our drivers. Can someone assist in counting the unique entries by account Number?
    My sheet will show Account No. in Col G starting from G4.
    I have found =IF(G4<>G3,COUNTA($G$3:G4)&".","") but this has the fault in that if I have 3 deliveries to an address, the next delivery drop count is incremented by 3 and not by 1.
    1. 1234 Fred
    ....1234 Fred
    ....1234 Fred
    4. 5678 Barney
    5. 9101 Wilma

    Any assistance or pointing in the right direction greatfully recieved.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,461
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    What is the desired result for the sample you provided?

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    1. 1234 Fred
    ....1234 Fred
    ....1234 Fred
    2. 5678 Barney
    3. 9101 Wilma

    (Obviously the .... are just spacers )

    Thanks Aladin

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,461
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    Quote Originally Posted by Northerner
    1. 1234 Fred
    ....1234 Fred
    ....1234 Fred
    2. 5678 Barney
    3. 9101 Wilma

    (Obviously the .... are just spacers )

    Thanks Aladin
    Hmm. Three is the desired result, I guess.

    =SUMPRODUCT((AccountRange<>"")/COUNTIF(AccountRange,AccountRange&""))

  5. #5
    Board Regular
    Join Date
    Nov 2002
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    Aladin,
    Dooh!
    Thanks for the response but I obviously didn't make my request clear

    Drivers, being drivers, (Ours at least before anyone shoots me) need to know that Fred (1234) is going to be his first drop, Barney (5678) is going to be his second, and in turn Wilma will be his third.
    Your formula counted the Unique account numbers while what I need is to have 1 against Fred (for first drop) 2 against Barney (for second) etc.
    I could get my formula to put 1 against Fred but would count by rows until finding Barney and giving him 4(fourth drop) instead of 2(second drop).
    Hope this clarifies my request. Thanks for your time

  6. #6
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,637
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    If I understand correct paste this in F3 and drag down
    =IF(G3<>G2,COUNTIF($F$2:F2,">0")+1,"")

  7. #7
    Board Regular
    Join Date
    Nov 2002
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    Unfortunatly not Fairwind, this copies thje same results as my original formula up the top, =IF(G4<>G3,COUNTA($G$3:G4)&".","").
    Thanks for the attempt.

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,461
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    Quote Originally Posted by Northerner
    Aladin,
    Dooh!
    Thanks for the response but I obviously didn't make my request clear

    Drivers, being drivers, (Ours at least before anyone shoots me) need to know that Fred (1234) is going to be his first drop, Barney (5678) is going to be his second, and in turn Wilma will be his third.
    Your formula counted the Unique account numbers while what I need is to have 1 against Fred (for first drop) 2 against Barney (for second) etc.
    I could get my formula to put 1 against Fred but would count by rows until finding Barney and giving him 4(fourth drop) instead of 2(second drop).
    Hope this clarifies my request. Thanks for your time
    Array-enter & copy down...

    =IF(G3<>G2,COUNTDIFF(IF($G$3:$G$7=G3,$H$3:$H$7))-1,"")

    This formula requires the morefunc add-in.

    If this is also off the mark, I'd like see a sample and desired results along with the ranges of interest.
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

    If I've missed something, sorry, but the below seems to give what you require:-



    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    11234Fred
    2
    11234Fred
    3
    11234Fred
    4
    25678Barney
    5
    39101Wilma
    Sheet1

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  10. #10
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,637
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delivery Drop No.?

      
    Maybe Im wrong but as it counts the numbers in F column and not entrys in G column I thougt i got the answer you wanted.

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
  •  

 

 
DMCA.com