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.
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.