Forum Discussion

KIMTI null's avatar
KIMTI null
Copper Contributor
Dec 13, 2023

Extract data based on date range

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

   

 

Screenshot 2

 

 

 

  • mtarler's avatar
    mtarler
    Silver Contributor
    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, "") )
    • KIMTI null's avatar
      KIMTI null
      Copper Contributor
      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
      • mtarler's avatar
        mtarler
        Silver Contributor
        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)

Resources