Extract data based on date range

Copper Contributor

I have the datasheet with each data set recorded in the columns (not rows). This data sheet has multiple years (recorded by the date). I need to extract the data (specific rows) to another sheet by the date range. Below is screenshot 1 of one data set (one column). I need to extract the following rows based on the date range I have in two cells in the new sheet based on screenshot 2. I need to configure data in rows from each column between the date range specified. I apologize if this is confusing. Please let me know if I can provide further clarification. 

 

Screenshot 1

KIMTInull_0-1702507210792.png   

 

Screenshot 2

KIMTInull_1-1702507462774.png

 

 

 

9 Replies
I think you might want something like:
=TRANSPOSE(FILTER(Sheet1!A1:Z100, Sheet1!A1:Z1>=$B$1, Sheet1!A1:Z1<=$C$1, "") )
but if you only want certain rows from sheet1 then add a CHOOSEROWS() like:
=TRANSPOSE(FILTER(CHOOSEROWS(Sheet1!A1:Z100,1,4,6,7), Sheet1!A1:Z1>=$B$1, Sheet1!A1:Z1<=$C$1, "") )
Thank you for the response. It works well, if I want my sheet 2 (new sheet) to show me the data in columns. I want the sheet 2 to show the data in rows only. for example.
05-Oct-23
331620
First Aid
Fall
Next data set
07-Oct-23
331739
Near Miss
Trip
next data set and so on
you should be able to use TOCOL to do that so maybe try:
=TOCOL(FILTER(CHOOSEROWS(Sheet1!A1:Z100,1,4,6,7), Sheet1!A1:Z1>=$B$1, Sheet1!A1:Z1<=$C$1, "") , ,TRUE)
Thank you again. I used the following formula:
=TOCOL(FILTER(CHOOSEROWS('2.Know'!A1:ZZ100,3,6,8,18), '2.Know'!A3:ZZ3>=$B$1, '2.Know'!A3:ZZ3<=$C$1, ,TURE) ) and it gave me the folllowing error: You have entered too many argument for this function. I appreciate if you can assist further on this.

@KIMTI null 

you entered:

=TOCOL(FILTER(CHOOSEROWS('2.Know'!A1:ZZ100,3,6,8,18), '2.Know'!A3:ZZ3>=$B$1, '2.Know'!A3:ZZ3<=$C$1, ,TURE) ) 

I don't know what that in red at the end is.  After that last $C$1 should be the end of the FILTER function with maybe a if not found (e.g.  ,"" ) and then should be the end of the TOCOL function with ,,TRUE)

so maybe try:

=TOCOL(FILTER(CHOOSEROWS('2.Know'!A1:ZZ100,3,6,8,18), '2.Know'!A3:ZZ3>=$B$1, '2.Know'!A3:ZZ3<=$C$1, ""),,TRUE)

 

Thank you, It worked perfect.
Sorry, I noticed that the start of new data followed the start of date range, but it did not stop the data on new sheet at the date ranged as specified specified. Data is copied all the to the end of the date in sheet 1.
oh my bad try this:
=TOCOL(FILTER(CHOOSEROWS('2.Know'!A1:ZZ100,3,6,8,18), ('2.Know'!A3:ZZ3>=$B$1)*( '2.Know'!A3:ZZ3<=$C$1), ""),,TRUE)
Thank you. All is good now.