Copying data from one table column to another if it falls in a specific date range

If you look at the attached file. The running total sheet has a table that I will be continuously uploading similar data to for the year. I need to break that data down into each month in separate sheets. The sheet "How March should look" is what I need the sheet "march" to look like after conditional formatting is applied. I need it to copy and paste the part number column from the table in running total to the part number column in the table in the march sheet. But I only need the part numbers that occur in march. I then need the subsequent descriptions and costs to be copied over for those part numbers. I found one formula that somewhat worked but it left 0s in the cells for which the part number did not exist in march date range. I would like for all of this to happen automatically as I load data in the running total table.

Thank you

I was able to get close with this, but not exactly what I need. Could you go more in depth with how I should set up this filter?

Yes, this is a very simplified version of a project for work. I have a macro I will run that will remove the duplicates after I have the part numbers that occurred in each month.

This will only return the first row of the table on the running total sheet,and that part number does not occur in march.

This is close, but I need the table on "march" to be linked to the table on running total. Where the part #s are automattically added if they fall in to that month. And then continues to do so as more data is entered, as I will be doing this for each month

Yes, if it can be hidden.

I may have attached your excel file, now macro enabled, (first time so anything could happen)
You will see it has another sheet "March_1".

This sheet has a macro attached which will run every time you select the sheet.

To create other months copy the sheet and change the dates.

It is very basic in order to, perhaps, make it easier for you to follow.

there is no validation against the part number and unit cost (It will use the first instance of the unit cost)

If you don't like it running when you open the sheet then move all the code to a module and add a button to drive it.

Good luck

This is great, but I need to build my own spreadsheet at work. Could you give me an overview of how you recorded this macro?

Originally Posted by fireking14

This is great, but I need to build my own spreadsheet at work. Could you give me an overview of how you recorded this macro?

Hi fireking14
There is nothing recorded in this macro it was constructed by hand.

I started by getting the dates and storing them in a variable.
You can get an idea of the code by recording select the dates and copying to another cell. You, obviously do not want to do a copy but it will show you how to address a cell.

As the method, I chose, to combine like part numbers required looking for previous occurrences each run had to start with an empty sheet otherwise each time it runs it adds the same pars to itself.
Therefore the next bit is to clear all the data after the header row. There are heaps of ways to do that and you could record selecting A2 the shift >cntl > end keys and pressing delete.

I then needed to know how many rows there are on the input sheet to control the loop.
You could possibly record selecting the top left of your input to the bottom left of your input.

What you need from this is the row number.

I then set up some variables to keep track of or the input and output rows.

I then created a loop starting with the first data row on the input sheet
First checking the data is within the range on the output sheet.
If not go to the next row.

When I find a compliant record (within the date range) on the input sheet I look on the output sheet to see I have seen that part number before

If I have then I add the occurrence value from input to existing value on the output and also perform the multiplication although you could leave this as a formula

If there is no previous occurrence then I add a new line copying the required data from input to output and perform the calculation cost x occurrence.
Also, this step adds 1 to the variable holding the location of the next available output line.

Option Explicit

Please Login or Register  to view this content.