How to extract cells between start and end dates

[alexisis]

Hello,

I have list of data, several rows and columns. Column F has a date and I would like to be able to do 2 things

1. Copy all rows matching start and end date (F) to a new tab. Basically copy/paste but only lines that are between the range of dates
2. Similar to point 1, but provide summary of items in D column with with total quantity (I) between start and end dates

Example for point 2:

8043S0010 - 3 lines, with values in I column 7, 40, 60. On the output show one line 8043S0010 with total of 47 for dates 20-04-20 (F column).


Attached is a sample file of raw data.

Thank you!

[Limor_OP]

Hi,
I will start with the 2nd request

In L2 and down:
=INDEX($D$1:$D$41,AGGREGATE(15,6,(ROW($D$2:$D$41))/(MATCH($D$2:$D$41,$D$2:$D$41,0)=ROW($D$1:$D$42)),ROWS($M$1:M1)))

In M2 and down:
=SUMIF(D:D,L2,H:H)


Now regarding section 1 :
what do you mean start and end date?
they are both in same column? and what is the range you refer to?

You can upload your file with manual entered results to clarify what you are intending to achieve.

[alexisis]

Hello,

2nd request worked like a charm

As for section 1, you can use the same file as attached in the first post.

Example

For dates below in column F, copy all lines that are between (and including) 12-03-20 and 17-04-20 in column F. So those with date 13-05-2020 would be omitted and not copied. On the output, it should be more or less identical file but only with lines between chosen dates.

12-03-20
12-03-20
17-04-20
13-05-20
13-05-20
13-05-20
13-05-20
17-04-20



I hope this helps. Thank you!

[Limor_OP]

But in your example the dates are organized in ascending way, and in your file it is not organized in chronologic order,
so do you attempt to find the smallest and the largest dates as your range, and check each date to see if it falls between this range?

[alexisis]

I think I am not explaining myself good enough.

Something similar, if not the same it described at the link below. Tried that, but cannot get it work for unknown reason. It keeps saying that the formula is incorrect.

Please Login or Register  to view this content.

[Limor_OP]

Maybe like this?
In sheet1 cell A3 and down (where the smallest date is 12/03/2020 and largest date is 25/11/2020


=IF(AND(tmp054605459!F2>=(MIN(tmp054605459!F:F)),tmp054605459!F2<=(MAX(tmp054605459!F:F))),tmp054605459!F2,"")

[alansidman]

Here is an alternative solution using Power Query. The Mcode is below. I selected two random dates for this exercise. Power Query is called Get and Transform in Excel 2016 and can be found on the Data Tab

Please Login or Register  to view this content.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[alexisis]

Hello Alansidman,

Would it be possible to update the code to read from source that has more columns than original sample file? I am attaching one more file. The original one had less columns but the new one is the one I would use on a daily basis. Also, would it be possible to have 2 outputs? One tab with what you just made and second tab with aggregated result similar to belinda200 suggested in scenario 2 (below?)

In L2 and down:
=INDEX($D$1:$D$41,AGGREGATE(15,6,(ROW($D$2:$D$41))/(MATCH($D$2:$D$41,$D$2:$D$41,0)=ROW($D$1:$D$42)),ROWS($M$1:M1)))

In M2 and down:
=SUMIF(D:D,L2,H:H)

Thanks so much

[JeteMc]

I believe that the following does what you want.
The power query advanced editor code that produces the table on sheet1 is basically the way that Alan showed in post #7

Please Login or Register  to view this content.

The formulas belinda wrote are modified to produce the output on sheet2
1. =IFERROR(INDEX(Sheet1!$D$1:$D$16,AGGREGATE(15,6,(ROW($D$2:$D$16))/(MATCH(Sheet1!$D$2:$D$16,Sheet1!$D$2:$D$16,0)=ROW($D$1:$D$16)),ROWS(A$2:A2))),"")
2. =IF(A2="","",SUMIF(Sheet1!D:D,A2,Sheet1!I:I))
Let us know if you have any questions.