this is my prob

this is a list that i want to keep track of the job that has been done
on a vehicle.

The vehicle can come into the workshop twice a day for different
problems but i

want to assign a unique job sheet no that is carried on the vehicle.

The scenario:

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005
engine repair


but if there is an input in date out for x123 and x123 comes into the
workshop,
a diffrent ID will be assigned

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005 01/10/2005 engine
repair
3 x123 01/10/2005
brake repair

right now , i am using the contenuate of the vehicle no and the date in
to create the unique ID but this will not create a unique Id if it comes
into teh workshop twice a day.

hope that this is clear.

is there such a formula?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

In A2 enter 1 - always 1 for the first item

In A3:
=IF(B3="","",IF(AND(SUMPRODUCT(--($B$2:B2=B3),--($C$2:C2=C3))0,SUMPRODUCT(-
-($B$2:B2=B3),--($C$2:C2=C3),--($D$2:D2<""))=0),INDEX($A$2:A2,MATCH(B3&C3,$
B$2:B2&$C$2:C2,0)),MAX($A$2:A2)+1))

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cjjoo" wrote in
message ...

this is my prob

this is a list that i want to keep track of the job that has been done
on a vehicle.

The vehicle can come into the workshop twice a day for different
problems but i

want to assign a unique job sheet no that is carried on the vehicle.

The scenario:

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005
engine repair


but if there is an input in date out for x123 and x123 comes into the
workshop,
a diffrent ID will be assigned

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005 01/10/2005 engine
repair
3 x123 01/10/2005
brake repair

right now , i am using the contenuate of the vehicle no and the date in
to create the unique ID but this will not create a unique Id if it comes
into teh workshop twice a day.

hope that this is clear.

is there such a formula?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

BTW, my formula is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cjjoo" wrote in
message ...

this is my prob

this is a list that i want to keep track of the job that has been done
on a vehicle.

The vehicle can come into the workshop twice a day for different
problems but i

want to assign a unique job sheet no that is carried on the vehicle.

The scenario:

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005
engine repair


but if there is an input in date out for x123 and x123 comes into the
workshop,
a diffrent ID will be assigned

unique_id vehicle no date_in date_out action taken
1 x123 01/10/2005
engine repair
2 y456 01/10/2005
chasis repair
1(auto) x123 01/10/2005 01/10/2005 engine
repair
3 x123 01/10/2005
brake repair

right now , i am using the contenuate of the vehicle no and the date in
to create the unique ID but this will not create a unique Id if it comes
into teh workshop twice a day.

hope that this is clear.

is there such a formula?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

this formula does not work if the first two entry is x123. Is there a
solution to it?

and will this formula work if the same vehicle comes into the workshop
three times a day (date in date out)?


tks for the prompt reply.


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

How odd!

You need a different formula for A3 only

=IF(AND(B3=B2,C3=C2,D3=""),A2,A2+1)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cjjoo" wrote in
message ...

this formula does not work if the first two entry is x123. Is there a
solution to it?

and will this formula work if the same vehicle comes into the workshop
three times a day (date in date out)?


tks for the prompt reply.


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

but what sohuld i do if x123 comes back into the workshop more than two
times

in a single day ? (date in and date out)

the formula does not seems to work . hope that you can give me some
advise . tks

the key thing is that once a vehicle has been dated out , the next time
it comes

into the workshop, a different ID will be issued and the vehicle will
be using the

new id until it is dated_out again.

complicated?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

It works as I understand the data.

Explain what the data looks like now, or the possible combination, what
formulae you have where, and how it doesn't work.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cjjoo" wrote in message
...

but what sohuld i do if x123 comes back into the workshop more than two
times

in a single day ? (date in and date out)

the formula does not seems to work . hope that you can give me some
advise . tks

the key thing is that once a vehicle has been dated out , the next time
it comes

into the workshop, a different ID will be issued and the vehicle will
be using the

new id until it is dated_out again.

complicated?


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile:
http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417

this is the result that i obtained:


id veh date in date out
1 x123 01/10/2005
2 y456 01/10/2005
1 x123 01/10/2005
1 x123 01/10/2005
1 x123 01/10/2005 01/10/2005
3 x123 01/10/2005
4 x123 01/10/2005 01/10/2005
5 x123 01/10/2005
6 x123 01/10/2005

in row 8,9,10,(last three rows) thre result that is desired is

3 x123 01/10/2005 01/10/2005
4 x123 01/10/2005
4 x123 01/10/2005

i dont know what has gone wrong

in A3 : formula used =IF(AND(B3=B2,C3=C2,D3=""),A2,A2+1)
in A4 : =IF(B4="","",IF(AND(SUMPRODUCT(--($B$2:B3=B4),--($C$2:C3=C4))

0,SUMPRODUCT(--($B$2:B3=B4),--($C$2:C3=C4),--($D$2:D3<""))=0),INDEX

($A$2:A3,MATCH(B4&C4,$B$2:B3&$C$2:C3,0)),MAX($A$2: A3)+1))


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=477417