Vlook up values and saving them

[need4excel]

Hi guys,

I need help on this.

I would like to tie and record the car's plate number and turn-around-time for each stage but there's a catch. Read on please.

For example: 3 person brings their car in for cleaning and drying.

Cleaning stage:

Name	Number Plate	Time of entry
Car 1	2651	12:00pm
Car 2	1554	12:05pm
Car 3	3223	12:06pm

<tbody>
</tbody>

Car 1 and 2 move to the next stage which is drying, hence, Car 1 and 2 will disappear from the Cleaning Table.

Drying Stage:

Name	Number Plate	Time of entry
Car 1	2651	12:04pm
Car 2	1554	12:11pm

<tbody>
</tbody>

Drying Stage shows the time of entry too. With that, we are able to calculate the time taken to wash the car.

Number Plate	Cleaning
2651	4 mins (A)
1554	6 mins (C)

<tbody>
</tbody>

Provided the Car 1 and 2 doesn't disappear from the table, the (A) formula would be =vlookup(2651,drying stage table, 3,0) - vlookup(2651, cleaning stage table, 3,0) Is this formula correct? It goes the same for (C) as well.

But the problem is, Car 1 and Car 2 will disappear from Cleaning Table after cleaning.

How do I store the data upon entry so it wouldn't give a #N/A and to be able to calculate the turn-around-time at the same time? Is there a workaround it?

PS: I've also tried the "save the last entry" through macro but it only works for the particular cell. It would be perfect if I could tie the macro to the number plate, if that's ever possible.
The macro formula I got is from this: https://www.extendoffice.com/documents/excel/5056-excel-remember-save-previous-cell-value.html

Thank you for your time and help!!

 

[Special-K99]

Why don't you have just one table?

Name	Number plate	Cleaning time	Drying time	Duration
Car 1	2651	        12:00           12:04           00:04
Car 2	1554            12:05           12:11           00:06
Car 3	3223            12:06

That way the cars never disappear unless you delete them and all the data is available.
Just looking at the above table I can see Car 3 hasn't entered the drying stage yet

 

[need4excel]

Thanks for your reply. The problem here is there’s another macro placed in the system (created by previous employee) to automactially move the cars over to the next stage