Multiple lookup and Dates

Hello

I have two work sheets in the same spreadsheet.

1) Transaction download for Fuel with, Car Reg, Transaction Date, Amount

2) Asset Tracker with, Car Reg, Employee Code, Date Owned From, Date owned To.
(The Car can have been owned by more than one Employee but not at the same time)

I want to allocate the Employee Code to each Transaction on the Transaction worksheet for the relevent dates.

Can you help?

Thanks

Robo

Originally Posted by Robo

Hello

I have two work sheets in the same spreadsheet.

1) Transaction download for Fuel with, Car Reg, Transaction Date, Amount

2) Asset Tracker with, Car Reg, Employee Code, Date Owned From, Date owned To.
(The Car can have been owned by more than one Employee but not at the same time)

I want to allocate the Employee Code to each Transaction on the Transaction worksheet for the relevent dates.

Can you help?

Thanks

Robo

The only common data seems to be the Car Reg. But can there be different employee codes for the same Car Reg?

Yes there can, a car can be owned by different employees, but not at the same time. This info is on the Asset Tracker worksheet.

Hope that helps!!

Robo

Originally Posted by Robo

Yes there can, a car can be owned by different employees, but not at the same time. This info is on the Asset Tracker worksheet.

Hope that helps!!

Robo

And is there a relation between the transaction date and Date owned from and date owned to?

Yes.

The lookup will be the Car Reg on the Transaction worksheet.

The Asset Tracker worksheet has the Car Reg, The Employee Code, Date Owned From, Date Owned To.

I want to put an Employee Code on each transaction on the Transaction Worksheet. This is easy if the car was owned by just one employee. However, a car can have two or three owners. Therefore the transaction date would have to fall between the dates owned by an employee to allocate the correct Employee Code.

Thanks for your help.

Robo

It seems you have a Many-to-many relationship which will not allow to do what you are after.
Sorry to say ( or I'm awfully mistaken) that this is due to bad database design.
Anyway, if you can post a sample, maybe something can be done.

I have attached a example for you.

Many Thanks

Robp

I'm afraid I couldn't find a solution.
I strongly suggest you review the design of your database and normalize it as much as possible.
Using a Vlookup it is quite simple to allocate the employee codes were the combination Car Reg - Employee Code is unique ( in Asset Tracker).
But multiple employees with multiple Car Regs
If somebody finds a solution to this one I will be very interested

There's a challenge!!

Thanks anyway

Robo

Hi,

Is the attached what you needed ...?

HTH
Carim

The attached file is what I need. No solution as yet. I'm sure there is?

Thanks for any help.

Hi again,

I have attached in my previous post an array formula which brings back the employee code ... (file name is lookupdates.xls)
Is it what you needed ... ?

HTH
Carim

Fantastic!! Very impressed!!

Thanks for your help. Its been driving me mad all day!

Robo

Glad it fixed your problem ...

Be careful in column B of transactions, there are some blanks which prevent proper matching with asset tracker ...
and asset tracker does not have all the dates, especially for GH02ZBZ and for
RB02SBZ ...

Thanks for the feedback
Carim

Yes, I also spotted the blanks.

Hi,

Forgot to mention that there are also some input dates which are not formatted properly, which prevents the array formula from returning the employee code ...

HTH
Carim

Also spotted those.
Thanks