Delivery Drop No.?

Thanks:  0
Likes:  0

1. ## 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. ## Re: Delivery Drop No.?

What is the desired result for the sample you provided?

3. ## Re: Delivery Drop No.?

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

(Obviously the .... are just spacers )

4. ## Re: Delivery Drop No.?

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

(Obviously the .... are just spacers )

Hmm. Three is the desired result, I guess.

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

5. ## Re: Delivery Drop No.?

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. ## 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. ## 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. ## Re: Delivery Drop No.?

Originally Posted by Northerner
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.

9. ## 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
 A2A3A4A5 =

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. ## 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

#### Posting Permissions

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