Delivery Drop No.? :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Delivery Drop No.?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

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.

Post Wed May 28, 2003 10:23 am 
 View user's profile Send private message Send e-mail

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

What is the desired result for the sample you provided?

Post Wed May 28, 2003 11:10 am 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

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

(Obviously the .... are just spacers icon_confused.gif )

Thanks Aladin

Post Wed May 28, 2003 11:14 am 
 View user's profile Send private message Send e-mail

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

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 icon_confused.gif )

Thanks Aladin


Hmm. Three is the desired result, I guess.

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

Post Wed May 28, 2003 11:26 am 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

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

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

Post Wed May 28, 2003 11:41 am 
 View user's profile Send private message Send e-mail

fairwinds
Board Master
Board  Master


Joined: 15 May 2003
Posts: 1191

Flag: Sweden

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

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

Post Wed May 28, 2003 11:49 am 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

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. icon_smile.gif

Post Wed May 28, 2003 12:10 pm 
 View user's profile Send private message Send e-mail

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

quote:
Originally posted by Northerner:
Aladin,
Dooh!
Thanks for the response but I obviously didn't make my request clear icon_redface.gif

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.
_________________
For MOREFUNC.XLL, see http://longre.free.fr/english/index.html
For more on morefunc, see Function Descriptions

Last edited by Aladin Akyurek on Wed May 28, 2003 12:17 pm; edited 1 time in total

Post Wed May 28, 2003 12:17 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3120
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

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.

Post Wed May 28, 2003 12:17 pm 
 View user's profile Send private message

fairwinds
Board Master
Board  Master


Joined: 15 May 2003
Posts: 1191

Flag: Sweden

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

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

Post Wed May 28, 2003 12:19 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3120
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

Incidentally, does anyone know why I'm getting blanks with the following formula, despite the function returning the correct answer when you go into the Insert Function dialog?

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
 
1234Fred 
3
 
1234Fred 
4
 
5678Barney 
5
 
9101Wilma 
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.

Post Wed May 28, 2003 12:23 pm 
 View user's profile Send private message

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

The Mudface question...

A2:

=IF(B2=B1,"",MAX($A$1:A1)+1)

Post Wed May 28, 2003 12:37 pm 
 View user's profile Send private message

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3120
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

quote:
Originally posted by Aladin Akyurek:
The Mudface question...

A2:

=IF(B2=B1,"",MAX($A$1:A1)+1)


That's the one, I got a circular reference, ignored it and forgot to correct it later on icon_mrgreen.gif . Thanks, Aladin.

Post Wed May 28, 2003 12:39 pm 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

Thanks for the interest guys,
Mudface's end result is the closest, but I would prefere only one '1' against the first Fred.
I'd love to dump an example but unfortunatly at work we run a Thin Client system.
If we (sorry you icon_redface.gif ) can't resolve this afternoon, I'll use Colo's Utility from home this evening.
:pary: but I have every confidence in you. icon_mrgreen.gif

Post Wed May 28, 2003 1:19 pm 
 View user's profile Send private message Send e-mail

Mudface
MrExcel MVP


Joined: 19 Feb 2002
Posts: 3120
Location: Sunny, spring-like Hull
Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

Well, with Aladin's correction, this seems to work as you wanted:-

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
 
1234Fred 
3
 
1234Fred 
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.

Post Wed May 28, 2003 1:21 pm 
 View user's profile Send private message

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

quote:
Originally posted by Northerner:
Thanks for the interest guys,
Mudface's end result is the closest, but I would prefere only one '1' against the first Fred.
I'd love to dump an example but unfortunatly at work we run a Thin Client system.
If we (sorry you icon_redface.gif ) can't resolve this afternoon, I'll use Colo's Utility from home this evening.
:pary: but I have every confidence in you. icon_mrgreen.gif


Can 1234 be combined with different names, not just with Fred?

Post Wed May 28, 2003 1:21 pm 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

The 1234 & Fred are just meant to represent a customer account number and name

Post Wed May 28, 2003 1:27 pm 
 View user's profile Send private message Send e-mail

Aladin Akyurek
.


Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag: Blank

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

quote:
Originally posted by Northerner:
The 1234 & Fred are just meant to represent a customer account number and name


Then Chris's (Mudface) formula as amended should work. Gee, you made me run, Northerner. The data must be sorted though.

Post Wed May 28, 2003 1:31 pm 
 View user's profile Send private message

Northerner
Board Master


Joined: 14 Nov 2002
Posts: 138

Flag: England

Status: Offline

 Reply with quote  

Re: Delivery Drop No.?

Mudface, Aladin and Fairwind, thanks for your input.
Yes Aladin, Mudface's was right, I'd dismissed it as the first drop number was not a formula but a static '1'. My program inserts new lines and copies formulie for each new 'delivery'. After you made me look at it again I realised I need only insert a blank line BEFORE my first delivery with a zero in it, then I could use Mudface's formula and have it copied each time it was required. (Well I know what I mean icon_laugh.gif )

Thanks again to you all!!

And my next problem is..............................? icon_mrgreen.gif

Post Wed May 28, 2003 1:56 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.