Infill Values based on ID and Date

[chergh]

Hi All,

I have 3 columns. AssetID, Signed Off Date, %Error

From this I want to create a table which gives me the % error for each day in a range for each asset ID.

So the data in the table could look like (dates are UK format not that it should matter):

AID1, 01/01/2016, 3%
AID1, 01/05/2018, 6%
AID1, 06/06/2018, 2%
AID1, 10/03/2019, 1%
AID2, 09/01/2019, 4%
AID2, 01/05/2019, 3%
AID3, 15/01/2017, 7%


For the output I require I need to convert the AssetID to a column heading (simple stuff). I then need to list each date in a defined period (usually financial year) in rows. I then need to return a value for each date based on the reported error. So for AID1 between: 01/04/2018 and 30/04/2018 I would look to return a value of 3%, between 01/05/2018 and 05/06/2018 a value of 6%, between 06/06/2018 and 09/03/2018 a value of 2% and between 10/03/2019 and 21/03/2109 a value of 1%.


I know how to do this in VBA inefficiently but the spreadsheet is several thousand rows in length and looping through that 365 times isn't my preferred solution. If anyone has any advice that would be great. I'm currently using Excel 2010 but should be upgraded to Office 365 pro plus in the next month or two. Thanks for any help.

 

[steve the fish]

So what is it you are after? A formula? Do you already have the structure of the new table set out?

 

[chergh]

Sorry I wasn't clear enough. I am looking to produce this, though the date range would be continuous in reality.