6 Replies Latest reply: Jun 16, 2015 2:11 AM by Madhu B RSS

    Comparing two data sets



      I have two sets of data like this


      Ref Data:

      Field1, Field2, Field3, Amt1, Amt2

      A,      M,     P1,     2,     1

      A,      M,     P2,     2,     1

      A,      F,     P3,     2,     1

      A,      F,     P4,     2,     1

      A,      I,     P5,     2,     1

      A,      I,     P6,     2,     1

      B,      M,     P2,     2,     2

      B,      M,     P7,     2,     2

      B,      I,     P5,     2,     2

      B,      F,     P3,     2,     2

      C,      M,     P2,     2,     3

      C,      M,     P7,     2,     3

      C,      I,     P5,     2,     3

      C,      F,     P3,     2,     3


      Now there is a listbox or multibox where the user picks the Field3.


      The requirement is if all the Field3 related to Field1 is picked then we need to take the avg of the amt1 and amt2 and sum it. For example if from the list box if I pick P1,P2,P3,P4,P5,P6 then it should take average sum of amt1 and amt2 for "A" in Field1. Else it should consider 0.


      Also if I pick P2,P7,P5,P3 then it should take avg sum of amt1 and amt2 for "B" and "C" and sum it together to give one value.


      How do I achieve this considering this ref data can be 1000s of lines and the listbox choice can range anywhere from 1 to all of them.


      I thought of "If Statement" but that wont work as the no of choices made in listbox is totally random.


      Can you kindly help me in this please. I am not sure how to progress.


      Thanks in advance