4 Replies Latest reply: Dec 5, 2017 5:31 PM by Leo Harper RSS

    Match against multiple criteria, ignoring filter etc...

    Leo Harper

      Hi everyone,

       

      I have been going nuts trying to figure this one out - any thoughts would be most welcome...

       

      I have my sample data below and attached in a screen shot, my source data is on the left, what I'm trying to produce is on the right.

       

      Brief explanation:

      I insert a filter and select 'Leo', the results I want are as shown. Basically, I want to see who else bought something on the same day from the same location. I've shown the results in what looks like a pivot table style, but I'm not wedded to the idea.

       

      I tried doing a pivot table (is there a way to make all the collapsed rows auto expand???), but this didn't work as when I filtered the customer name, it then stopped showing the other names in the results...help please...

       

            

      Purchase DateCustomerLocation of ItemItem
      21/01/2017LeoLeedsiPhone 7
      21/01/2017KateLeedsiPhone X
      21/01/2017BobLeedsipad Mini
      28/01/2017SarahNewcastleiPhone 7
      29/01/2017LeoManchesterGalaxy
      29/01/2017SarahManchesterXbox One
      02/02/2017BobBoltonipad Mini
      02/02/2017SarahBoltonGalaxy
      06/02/2017BobLeedsiPhone X
      06/02/2017SarahLeedsiPhone X
      10/02/2017DavidManchesterGalaxy

       

       

       

      Desired results:

       

          

      Filter selection: Leo
      21/01/2017LeedsKate
      Bob
      29/01/2017ManchesterSarah
      Filter selection: Sarah
      29/01/2017ManchesterLeo
      02/02/2017BoltonBob
      06/02/2017LeedsBob
      Filter selection: Bob
      21/01/2017LeedsLeo
      02/02/2017BoltonSarah
      06/02/2017LeedsSarah
      Filter selection: David
      -

      cheers,

       

      Pokes