Infill Values based on ID and Date

chergh

New Member
Joined
Nov 10, 2008
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
So what is it you are after? A formula? Do you already have the structure of the new table set out?
 
Upvote 0
Sorry I wasn't clear enough. I am looking to produce this, though the date range would be continuous in reality.

QZaW3mJ.png

WLJcc2N
WLJcc2N
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top