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

ID	Registration	Hire Start	Hire End	Festival
1	HN16 VNK	2/4/19	5/4/19	GREE
2	HN16 VNK	15/8/19	18/8/19	BOOM
3	R916 YMS	02/5/19	6/5/19	GEN
4

<tbody>
</tbody>

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

ID	Date	Registration	Festival	Driver Name
1	2/4/19	HN16 VNK	GREE
2	3/4/19	HN16 VNK
3	4/4/19	HN16 VNK	GREE
4	5/4/19	HN16 VNK
5	15/8/19	HN16 VNK	BOOM
6	16/8/19	HN16 VNK	BOOM
7	17/8/19	HN16 VNK	BOOM
8	18/8/19	HN16 VNK	BOOM
9	2/5/19	R916 YMS	GEN
10	3/5/19	R916 YMS	GEN
11	4/5/19	R916 YMS	GEN
12	5/5/19	R916 YMS	GEN
13	6/5/19	R916 YMS	GEN
14

<tbody>
</tbody>

 

[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