Hi all,
This forum is really helpful for novices like me. Currently struck with an
issue which goes as...

i have 3 col like date, cust number, quote ref. In quote ref i have a
formula "(COUNTIF($D$4:D16,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) Cust No. Quote Ref
01/10/2008 4628A 001
15/10/2008 4670B 001
16/10/2008 4628A 002

and so on,

Thanks in advance

This presumes that there will be at most, only a single order per any cust
per date

Cust Nos are assumed running in D4 down
In E4: =IF(D4="","",TEXT(COUNTIF(D$4:D4,D4),"000"))
Copy E4 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---
"Vijay DSK" wrote:
i have 3 col like date, cust number, quote ref. In quote ref i have a
formula "(COUNTIF($D$4:D16,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) Cust No. Quote Ref
01/10/2008 4628A 001
15/10/2008 4670B 001
16/10/2008 4628A 002
and so on

Max,
Thanks for your advice, but my actual situation is there will be more
quotations on a single day per customer
Advise me the suitable

"Max" wrote:

This presumes that there will be at most, only a single order per any cust
per date

Cust Nos are assumed running in D4 down
In E4: =IF(D4="","",TEXT(COUNTIF(D$4:D4,D4),"000"))
Copy E4 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---
"Vijay DSK" wrote:
i have 3 col like date, cust number, quote ref. In quote ref i have a
formula "(COUNTIF($D$4:D16,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) Cust No. Quote Ref
01/10/2008 4628A 001
15/10/2008 4670B 001
16/10/2008 4628A 002
and so on

.. my actual situation is there will be
more quotations on a single day per customer

Can you show how then should the quote refs be generated?
Take the sample data below for 2 customers, indicate in your reply:

Date_____ Cust QuoteR
1-Oct-08 4628A
1-Oct-08 4670B
1-Oct-08 4628A
15-Oct-08 4628A
15-Oct-08 4670B
15-Oct-08 4670B
16-Oct-08 4628A

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

Max,
My requirement is as follows, hope u can understand
Date_____ Cust QuoteR
1-Oct-08 4628A 001
1-Oct-08 4670B 001
1-Oct-08 4628A 001
15-Oct-08 4628A 002
15-Oct-08 4670B 002
15-Oct-08 4670B 002
16-Oct-08 4628A 003

Vijay DSK
"Max" wrote:

.. my actual situation is there will be
more quotations on a single day per customer

Can you show how then should the quote refs be generated?
Take the sample data below for 2 customers, indicate in your reply:

Date_____ Cust QuoteR
1-Oct-08 4628A
1-Oct-08 4670B
1-Oct-08 4628A
15-Oct-08 4628A
15-Oct-08 4670B
15-Oct-08 4670B
16-Oct-08 4628A

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

Dates are running in C4 down
In E4:
=IF(C4="","",IF(COUNTIF(C$4:C4,C4)1,"",COUNTIF(C$ 4:C4,C4)))

In F4:
=TEXT(COUNT(E$4:E4),"000")
Select E4:F4, copy down. The results you seek will appear in F4 down.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---
"Vijay DSK" wrote:
Max,
My requirement is as follows, hope u can understand
Date_____ Cust QuoteR
1-Oct-08 4628A 001
1-Oct-08 4670B 001
1-Oct-08 4628A 001
15-Oct-08 4628A 002
15-Oct-08 4670B 002
15-Oct-08 4670B 002
16-Oct-08 4628A 003

I assume your data start in row 4. You can use this formula in Row 4
for QuoteRef and copy.

=SUMPRODUCT(($A$4:A4=A4)*($D$4:D4=D4))

HTH
Kostis Vezerides

On Oct 31, 8:42*am, Vijay DSK
wrote:
Hi all,
This forum is really helpful for novices like me. Currently struck with an
issue which goes as...

i have 3 col like date, cust number, quote ref. *In quote ref i have a
formula "(COUNTIF($D$4:D16,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) * * *Cust No. * * * Quote Ref
01/10/2008 * * * * * * * * *4628A * * * * * *001
15/10/2008 * * * * * * * * *4670B * * * * * *001
16/10/2008 * * * * * * * * *4628A * * * * * *002

and so on,

Thanks in advance

Max,
Did exactly how u advised (changed the formula pointers also) the result is
as follows :
Col A Col B Col C Col D Col E Col F Col G
Sl No Date Cust Name Cust Code Cust Ref Quote Ref
1 10/09/2008 ABC 610000-RO Tele.conv 1 001
2 23/09/2008 ABC 611900 Tele.conv 1 002
3 26/09/2008 ABC 610902 E-mail 1 003
4 26/09/2008 ABC 611700 Tele.conv "" 003
5 26/09/2008 ABC 611700 Tele.conv "" 003
6 26/09/2008 ABC 611700 Tele.conv "" 003
7 26/09/2008 ABC 611702 Tele.conv 003
8 26/09/2008 ABC 611702 Tele.conv 003
9 27/09/2008 ABC 610409 Tele.conv 1 004
10 27/09/2008 ABC 610409 Tele.conv 004
11 01/10/2008 ABC 611501 Telecon 1 005
12 11/10/2008 ABC 610700 Tele.conv 1 006
13 22/10/2008 XYZ XYZ0004028 Tele.conv 1 007
14 23/10/2008 XYZ XYZ0004086 Er.Visit 1 008
15 23/10/2008 XYZ XYZ0004086 Er.Visit 008
16 23/10/2008 XYZ XYZ0004086 Er.Visit 008

Vezerid,
Take above example and after using your formula the result is as follows,
just go through the post what i am looking at please.
Col A Col B Col C Col D Col E Col F
Sl No Date Cust Name Cust Code Cust Ref Quote Ref
1 10/09/2008 ABC 610000-RO Tele.conv 1
2 23/09/2008 ABC 611900 Tele.conv 1
3 26/09/2008 ABC 610902 E-mail 1
4 26/09/2008 ABC 611700 Tele.conv 1
5 26/09/2008 ABC 611700 Tele.conv 2
6 26/09/2008 ABC 611700 Tele.conv 3
7 26/09/2008 ABC 611702 Tele.conv 1
8 26/09/2008 ABC 611702 Tele.conv 2
9 27/09/2008 ABC 610409 Tele.conv 1
10 27/09/2008 ABC 610409 Tele.conv 2
11 01/10/2008 ABC 611501 Telecon 1
12 11/10/2008 ABC 610700 Tele.conv 1
13 22/10/2008 XYZ XYZ0004028 Tele.conv 1
14 23/10/2008 XYZ XYZ0004086 Er.Visit 1
15 23/10/2008 XYZ XYZ0004086 Er.Visit 2
16 23/10/2008 XYZ XYZ0004086 Er.Visit 3

Request you both just copy & paste my data and advise me.

"vezerid" wrote:

I assume your data start in row 4. You can use this formula in Row 4
for QuoteRef and copy.

=SUMPRODUCT(($A$4:A4=A4)*($D$4:D4=D4))

HTH
Kostis Vezerides

On Oct 31, 8:42 am, Vijay DSK
wrote:
Hi all,
This forum is really helpful for novices like me. Currently struck with an
issue which goes as...

i have 3 col like date, cust number, quote ref. In quote ref i have a
formula "(COUNTIF($D$4:D16,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) Cust No. Quote Ref
01/10/2008 4628A 001
15/10/2008 4670B 001
16/10/2008 4628A 002

and so on,

Thanks in advance