1 Reply Latest reply on Jul 24, 2018 4:46 AM by kumar bharat

    Filtering across rows

    jon.baker

      Hi

       

      I have a dataset showing customers' product holdings in two time periods, 2017 & 2018.

       

      Each customer has two rows (one for each year) and each row contains customer ID, product holding and value - see a very simplified table below.

       

      CustomerYearProductValue
      AAA2017X10
      AAA2018Y15
      BBB2017X10
      BBB2018X10
      CCC2017Y15
      CCC2018Y15
      DDD2017Y15
      DDD2018X10

       

       

      I need to filter customers based on their 2018 product holding, but return both of the rows relating to the relevant customer. E.g. based on the data table above, I'd like to filter customers where their 2018 product holding is 'X' and return the following rows.

       

      CustomerYearProductValue
      BBB2017X10
      BBB2018X10
      DDD2017Y15
      DDD2018X10

       

      I've attached a packaged workbook with the same data in. How can I go about filtering customers based on the product in the relevant 2018 row?

       

      Thanks