Home » Forums » Open Source Learning Center » Question and Answer

Finding out who had a car at a specific date

Submitted by danschofield on 2 March, 2012 - 11:28

Hi,

My company has a fleet of over 500 cars and i need to work out who had a car at a specific date, as the employees change cars, leave the company, start the company very often.

I have a data sheet that tells me who had the car and from and to what date, i need to look at the reg in sheet 1, to the data sheet and also the date in sheet one for example 01/01/2011 and then for the formula to tell me who had that car at that date from the data in the data tab.

I have tried vlookup, if, and and formulas but keep getting #VALUE or #N/A, is there a formual or a range of formulas i can use to figure this out??

My sheets look like this below

Sheet 1

Vehicle Reg Month Name
KN10FHR 01/01/11 #VALUE!
KN10FHR 01/01/11 #VALUE!
KN10FHR 01/01/11 #VALUE!
KN10FHR 01/01/11 #VALUE!
KN10FHR 01/01/11 #VALUE!
KU09TUJ 01/01/11 #VALUE!
KU09TUJ 01/02/11 #VALUE!
KU09TUJ 01/03/11 #VALUE!
KU10XSV 01/08/11 #N/A
KU10XSV 01/08/11 #N/A
KU10XSZ 01/01/11 #VALUE!
KU10XSZ 01/01/11 #VALUE!
KU10XSZ 01/01/11 #VALUE!

Data

R_NUMBER NAME FROM TO
AU11WYG LANGLEY G 16/04/2010 12/08/2011
KU10XSV FROST M M 06/04/2010 05/04/2012
BJ58AXH RICHARDS P 06/04/2011 05/04/2012
BK11AFY KEMP J 06/04/2010 13/09/2011
BN58GXW TURNER G 06/04/2011 05/04/2012
DA59VUE QUILTER J J 16/05/2011 24/05/2011
DE59VGY SINCLAIR-ROSS S 13/04/2010 09/08/2011
KU10XSZ WILLIAMS G 14/04/2011 11/08/2011
DL10BZB BEAUCHAMP P 09/04/2011 08/08/2011
DL10ZBN MAGRATH T 06/04/2010 26/08/2011
DN10VDX MILLS S S 06/04/2011 16/09/2011
DS61MUV DINI H 31/01/2012 05/04/2012
DU59ZWY THEUNISSEN J 06/04/2011 24/06/2011
EF11VCY COPPING I 28/07/2011 26/08/2011
EF11VEP BRAIN D 24/08/2011 16/01/2012
EF11VFO FRIEND N 24/08/2011 05/04/2012
EF60HCC ROE A 31/05/2011 14/06/2011
EH11HCH BUTLER C 26/07/2011 05/04/2012
KU09TUJ SILVER H 17/10/2010 05/04/2012
EJ61XJD BRAIN D 17/01/2012 05/04/2012
EJ61XKE HOLMES J 17/01/2012 05/04/2012
KN10FHR LULHAM L 29/06/2010 23/09/2011
EN11HCF SINCLAIR-ROSS S 10/08/2011 30/09/2011
EN11HCJ MOORE B B 12/08/2011 05/04/2012
EN11HCO HUNT D 01/07/2011 05/04/2012
EN11HCU LANGLEY G 13/08/2012 16/03/2012
EN11HDE GRIFFITHS S 27/06/2011 03/02/2012
EN11HDV BEAUCHAMP P 09/08/2011 15/09/2011

‹ Use a custom function in any excel file Problem with OFFSET and Dynamic Chart ›
»

Who had the Car?

Submitted by or8 on 1 November, 2012 - 09:36.

Seems an ideal situation for a Pivot Table.

Highlight the data and choose Data - Pivot Table.

For the layout I would choose Name for the rows, R_Number, From and To for the columns and Count of R-Number for the Data.

I would also remove row totals as they cloud the issue.

(From your sample data some people have used more than 1 car but no single car has been used by more than 1 person)

Hope this helps

»