Filtering our sections of Data Based on Record ID

[Cbennett022]

Currently, I have assigned recordID's to rows, which are based on site address. I need to have each set of records, filtered out based on that record ID to perform additional analysis

The number of groupings, 6 as shown in the picture above, will vary every year.

Once I have the groupings separated from each other, I must then calculate the average book value for the year. The calculation should be as follows

for every row item that has a prior year date (2016) in the picture above, sum book value and that will become the Value for January.

That will then be the value for the following months, until a new row item is presented, which will then be added. (see below for example)

each grouping, based on recordID and site address will be exported into its own excel tab.

I have tried to create a batch Macro, multi formula's and a modified running total formulas to no avail.

Any help is appreciated

[neilgallen]

Attached workflow should get you there. Might be a simpler way to go about it, but it's been a long week and it's late.

(Note, I copied values from the image, so any inaccuracies in example are because I can't type. In the future attach a sample data set if you can. Always appreciated. I also did not include unnecessary fields.)

I've annotated the workflow for reference. In short:

Create a record ID based on address

Get a list of unique record IDs (since that list will change over time)

Filter out data by date after some formatting

Create a template sheet that's every date for every record ID, regardless of what's in the data

Join the actual data to that template sheet. There will be missing records

After figuring out prior year book value, join to template sheet and do a bit of formula work to fill in where necessary.

Let me know if you have any questions.

[Cbennett022]

First off, thank you very much for your help. This workflow is driving me crazy.

I have tried to use this solution with the entire dataset, which is included in the workflow attached below and do have a few follow up questions, if you don't mind.

Please Note, as I am trying to create this workflow for minimal human interaction, I added a few formulas is determine whether or not the date is for Current Year or Prior Year.

1) For some reason, recordID 5 in this instance is not coming out of the joins down the workflow, and I believe it is caused by only have prior year dates and not current year.

    I attempted to Union it with the rest of the dataset, but the details are missing. 

2) For the final formula tool in your workflow, I see how it is layering on the book value by month and not changing the value until a new value is introduced, however, I need for it to take the sum of the values once a new one is introduced. For example, in the image below, we have $13,469 for January and February, which is perfect, but once March's value is introduced, march would show $14,301 (13,469 +831). That would be the value to continue downward until another value arose. This would reset when a new recordID

starts. 

 

The ideal end result for each recordID would look similar to the image below

I may be pressing the envelope here, but is this something I could bury into a batch macro?

[neilgallen · Accepted solution]

@Cbennett022 Thanks for the clarification. This is simpler than I originally thought. All you want is a running total of the book value, just filtered down to the current year. Because the data set includes prior year, this will ensure that the running total in January will include all prior year values.

The attached workflow still creates a template dataset for each record ID and date to account for missing months in the original data, but then does a running total and filters down to the current year only. I believe this gets you what you were requesting. While it could be done in a batch macro, it's not needed.

[Cbennett022]

Thank you so much!