Cutting Data to the Latest 365 days for multiple sets of data

**improvemyknowledge** · 09-05-2018, 04:51 PM

Good Evening,

I am looking for some help if possible. I have attached some example data that shows what I am using - data ranges from 100 days sometimes to 1000 days, very unpredictable per customer number and there will always be 48 columns then across the top.

I am looking to see if there is a function, VBA, macro or filtering tool that can extract out/cut around the latest 365 days of data without having to look at them all manually. The data attached shows two customer examples but this can go up to 1000 different customers so I am very keen to see if I can improve the process I am using currently.

Any help would be greatly appreciated.

Thank you!

**FlameRetired** · 09-05-2018, 08:05 PM

Hi improvemyknowledge. Welcome to the forum.

For a formula solution with that much data I would use helper columns. The formula alternative would be many, many array formulas. They are resource hungry and I would be concerned they would slow the workbook.

The ouput section is in columns BD:DA of the attached. The headers are identical to the source data.

This has a system of 4 helper columns.

in column A are Indexing Reference numbers. These are done with the fill series feature under Home > Editing group. Since there is no calculation overhead I "future proofed" the range to handle additional data.
In column BB is another filled by series helper. It serves as the K value for the small function that comes later.
In column AZ is a helper that assigns Indexing numbers to the qualifying rows by way of the first helper in column A. The formula is
Formula:
```
Please Login or Register  to view this content.
```
and returns mostly blanks except for the last 365 rows.
In column is a helper that "gathers" all of those assigned index numbers once referencing the helper in column BB for the K values. Those index numbers will used by reference in the final formula rather than calculation. The formula
Formula:
```
Please Login or Register  to view this content.
```

The final formula in BD2 filled across and down until you get blanks

Formula:

Please Login or Register  to view this content.

**Pete_UK** · 09-05-2018, 08:20 PM

Dave,

I think you've missed the fact that there are TWO sets of customer data in the file, one for Cust Num 12345 (covering the period 01/08/2016 up to 17/10/2017) and the other for 54321 (covering the period 01/10/2016 up to 20/02/2018). The OP says there may be up to 1000 customers, and when I read that I thought that 365 days worth of data for each customer does not lend itself to a formula solution.

Pete

**FlameRetired** · 09-05-2018, 08:35 PM

Thank you, Pete. Yes I did miss that.

And of course you are correct. Formulas out.

**improvemyknowledge** · 09-06-2018, 03:17 AM

Thank you so much for trying with the formulas- it's very useful, I will give it a go with any single customer sets of data that I have to improve the process

**JeteMc** · 09-12-2018, 10:00 PM

I respectfully submit that there may be a formula solution.
Borrowing from Dave's idea about a helper column, sheet1 A2 and down is populated using:

Formula:

Please Login or Register  to view this content.

Sheet2 (2nd row and down) is populated using:

Formula:

Please Login or Register  to view this content.

Note that I only included the first five columns on Sheet 2 however dragging the formula to the right and then double clicking the fill handle while the additional columns are selected should populate the entire range.
Let us know if you have any questions.

Cutting Data to the Latest 365 days for multiple sets of data

LinkBack

Thread Tools

Rate This Thread

Display

Cutting Data to the Latest 365 days for multiple sets of data

Re: Cutting Data to the Latest 365 days for multiple sets of data

Re: Cutting Data to the Latest 365 days for multiple sets of data

Re: Cutting Data to the Latest 365 days for multiple sets of data

Re: Cutting Data to the Latest 365 days for multiple sets of data

Re: Cutting Data to the Latest 365 days for multiple sets of data

Thread Information

Users Browsing this Thread

Similar Threads

Finding the latest data multiple criteria are met

Split one row with data sets into multiple rows with one data set

If the data is duplicate, then vlookup for the latest date of multiple data

VBA to fill down for multiple data sets, then consolidate data

[SOLVED] Ploting multiple data sets with non-aligning x-data

Dependable Data Validation; Multiple data sets

Aligning rows of data from multiple data sets in columns

Bookmarks

Bookmarks

Posting Permissions