headache! :) unique_id's

  • Thread starter Thread starter cjjoo
  • Start date Start date
C

cjjoo

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?
 
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)
 
BTW, my formula is an array formula, so commit with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
this formula does not work if the first two entry is x123. Is there
solution to it?

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


tks for the prompt reply
 
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)
 
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?
 
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)
 
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
Click to expand...

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