correct function/formula to return a row for each date of a vehicle hire - VBA? VLOOKUP? MATCH?

[lolbint]

hi

sorry new to forum and pretty new to VLOOKUP, VBA etc, have played with various things that half do what I want but can't find exact solution

I have two workbooks, one where vehicle hire booking's are stored there is a start date column, end date column and registration column, I then have a separate workbook where I want it to look at first workbook and create a row for each date between the start and end date and copy the registration over so I can enter drivers details and other stuff like have they done a drivers check that day, problem also being that in a season we may have same vehicle registration on hire multiple times the only unique identifier in these cases would be the festival name column.

pls help, I had the registration being returned in second workbook by using ='[2019 critical insurance.xlsx]HGV'!$B20

and start date being returned on one row using =VLOOKUP([@Registration],'[2019 critical insurance.xlsx]HGV'!$B$2:$K$35,6)

but then I am still having to manually do the rows for the other dates, this also then mucked up the registration lookup as I had overwritten the cells that contained $B2 through to $B19 so they were no longer returned, then as soon as I have the same registration twice it removed the first hire completely from the second work book

I then played with using a VBA code to run the dates but end up with similar problems, I also have problems when I try to use VLOOKUP to return the festival name due to registration appearing more than once it ignores the GREE entry and only returns the BOOM entry when using =VLOOKUP([@Registration],'[2019 critical insurance.xlsx]HGV'!$B$2:$M$35,11)

example of first workbook below

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Registration[/TD]
[TD]Hire Start[/TD]
[TD]Hire End[/TD]
[TD]Festival[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]HN16 VNK[/TD]
[TD]2/4/19[/TD]
[TD]5/4/19[/TD]
[TD]GREE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]HN16 VNK[/TD]
[TD]15/8/19[/TD]
[TD]18/8/19[/TD]
[TD]BOOM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]R916 YMS[/TD]
[TD]02/5/19[/TD]
[TD]6/5/19[/TD]
[TD]GEN[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


would it help to ID each row entry and in the first workbook and use that somehow, do I need to use INDEX/MATCH with VLOOKUP somehow

example of what I want second workbook to show below, I'm using excel 2016 on windows 10


[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Registration[/TD]
[TD]Festival[/TD]
[TD]Driver Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/4/19[/TD]
[TD]HN16 VNK[/TD]
[TD]GREE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/4/19[/TD]
[TD]HN16 VNK[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/4/19[/TD]
[TD]HN16 VNK[/TD]
[TD]GREE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/4/19[/TD]
[TD]HN16 VNK[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15/8/19[/TD]
[TD]HN16 VNK[/TD]
[TD]BOOM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16/8/19[/TD]
[TD]HN16 VNK[/TD]
[TD]BOOM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]17/8/19[/TD]
[TD]HN16 VNK[/TD]
[TD]BOOM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]18/8/19[/TD]
[TD]HN16 VNK[/TD]
[TD]BOOM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2/5/19[/TD]
[TD]R916 YMS[/TD]
[TD]GEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3/5/19[/TD]
[TD]R916 YMS[/TD]
[TD]GEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4/5/19[/TD]
[TD]R916 YMS[/TD]
[TD]GEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5/5/19[/TD]
[TD]R916 YMS[/TD]
[TD]GEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]6/5/19[/TD]
[TD]R916 YMS[/TD]
[TD]GEN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jim may]

Can you provide in your example the result(s) of the formula you seek to have?

 

[jim may]

Never mind; Looking further into your need I no longer have the question/request earlier submitted.

 

[lolbint]

sorry I'm confused by your reply, the second workbook example I have put in my post is what I wish to see so the column with date needs to populate based int he hire start/end dates in workbook 1

I think I'm getting closer but still struggling abit, I have now given each record in the first workbook an ID and have added a column in 2nd workbook to record this and populate from first workbook using =VLOOKUP('[2019 critical insurance.xlsx]HGV'!$A2,'[2019 critical insurance.xlsx]HGV'!$A:$A,1,0)

then all the other columns where I want the data like registration, festival name, vehicle type etc I have used a version of this formula =INDEX('[2019 critical insurance.xlsx]HGV'!$A$1:$L$28,MATCH($B2,'[2019 critical insurance.xlsx]HGV'!$A:$A,0),12)

this has now solved my problem with the duplicate reg/festival names being ignored but I still only have one row per date with the start date, am I best using VBA code to populate the other dates in rows below all the other cells that are automatically populated and then I can custom sort by date?

now I have put an id column on second worksheet I only have to fill this in for alot of the other fields to self populate now