Hey guys.
I will try and explain this in as much detail as I can and hope you understand!
So effectively i have a data set of around 370,000 rows of data, one row (price) for every minute of the year. However, there are many minutes that are missing and thus i need to flag them as missing by inserting the missing times & dates with a "N/A" instead of a price.
So, on a seperate sheet I have used the =sequence function to insert 1 to 525643 in rows, and then auto filled down the dates and times from 01/01/2020 17:00 to 31/12/2020 16:58.
I have then tried to use the =IFError(Vlookup(...)) function to bring over the prices that match a date and time, and insert "N/A" where there is not match.
However, my problem starts when i try to drag down the vlookup function. It works for the first few dates× that i wrote in myself or copied over from the orignial worksheet, however from the row that i dragged down it automatically returns the "N/A" rather than the relevant price. Even though i can see that the dates on the two sheets do in fact match.
I have attached some photos which may make this a bit clearer.
I auto filled by dragging down from row 17:05. As you can see, for example the 17:06 time stamp on both sheets is clearly the same format and date/time, however the vlookup does not seem to recognise the price in column B.
*I have realised this is an issue with the date formatting - when i chose a random time e.g 17.30, the formula tab presents it as 17:30:00 if i delete the 00 at the end, it suddenly returns the correct price value rather than the "N/A". However, it is not feasible to do this for half a million data points and hence i ask if there is a alternative method? I have tried to reformat the dates on both worksheets to be the same however this does not fix the issue.*
I hope that makes sense. Please let me know your thoughts and whether i am missing something!
I will try and explain this in as much detail as I can and hope you understand!
So effectively i have a data set of around 370,000 rows of data, one row (price) for every minute of the year. However, there are many minutes that are missing and thus i need to flag them as missing by inserting the missing times & dates with a "N/A" instead of a price.
So, on a seperate sheet I have used the =sequence function to insert 1 to 525643 in rows, and then auto filled down the dates and times from 01/01/2020 17:00 to 31/12/2020 16:58.
I have then tried to use the =IFError(Vlookup(...)) function to bring over the prices that match a date and time, and insert "N/A" where there is not match.
However, my problem starts when i try to drag down the vlookup function. It works for the first few dates× that i wrote in myself or copied over from the orignial worksheet, however from the row that i dragged down it automatically returns the "N/A" rather than the relevant price. Even though i can see that the dates on the two sheets do in fact match.
I have attached some photos which may make this a bit clearer.
I auto filled by dragging down from row 17:05. As you can see, for example the 17:06 time stamp on both sheets is clearly the same format and date/time, however the vlookup does not seem to recognise the price in column B.
*I have realised this is an issue with the date formatting - when i chose a random time e.g 17.30, the formula tab presents it as 17:30:00 if i delete the 00 at the end, it suddenly returns the correct price value rather than the "N/A". However, it is not feasible to do this for half a million data points and hence i ask if there is a alternative method? I have tried to reformat the dates on both worksheets to be the same however this does not fix the issue.*
I hope that makes sense. Please let me know your thoughts and whether i am missing something!
