Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
May 25, 2021

Excel how to copy out only specific data to another sheet?

hi   I have a main data and would like to only copy other another sheet when the column of the start date and expiry date has info in.   How to?   Main data has a lot of column - Description, N...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 25, 2021

bbsin 

It might be a good idea to specify the Excel version.  I would use something of the sort

= LET(
  selectedrows, FILTER(Table1, (Table1[Startdate]>0)*(Table1[Enddate]>0)),
  k, SEQUENCE(ROWS(selectedrows)),
  columnIndices, XMATCH(selectedHeaders, Table1[#Headers]),
  INDEX(selectedrows, k, columnIndices) )

but the process is built on functionality that is specific to Excel 365.

  • bbsin's avatar
    bbsin
    Iron Contributor
    May 26, 2021

    hi

    mine is 0365.  I try your formula not to sure the selectedrows is it for the new spread?   The - 

    selectedHeaders, Table1[#Headers]

     

    is it for the Main Data?  ColumnIndices is?   Is there a video that I can view?  

     

    Thanks 

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      May 26, 2021

      bbsin 

      I use Excel Tables and Structured References to reference the source data and Defined Names for the data that does not form part of a table.  The LET function allows the user to break formulas down into bite size portions, each referenced by name.

      • bbsin's avatar
        bbsin
        Iron Contributor
        May 27, 2021
        hi Peter,

        Thanks will give it a try

        Cheers

Resources