Worksheet Formula: Find value in an array

  • I've got a question about finding a value in an array. The following Table is called CustomerSubscription.


    I need to be able to tell, based on a CustomerID, whether he/she still has an active subscription. If yes,
    the value should be true, if not then it should obviously be false.


    For example, customer 1 has an active subscription - whereas customer 2 has none.


    What is the best way to solve this problem. I have no problems if each customer only has one subscription,
    but I'm unfamiliar on how to do this with arrays - that's what I have to use, right?


    I've attached the worksheet to this post.
    forum.ozgrid.com/index.php?attachment/66523/


    Thanks for your help.


    Massimo




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259
  • Re: Worksheet Formula: Find value in an array


    Not too sure exactly what you want, where the formula is to go, etc...


    For example, in Col G, Row 2:


    [bfn]=IF(COUNTIFS([CustomerID], [@CustomerID], [Active], TRUE), "Active", "")[/bfn]


    Your table will extend to incorporate Column G.


    The formula can be simply copied to existing rows and will automatically be included in new rows.

  • Re: Worksheet Formula: Find value in an array


    I did indeed forget to mention where the result should be. Apologies for that.


    I just want to be able to check whether a customer has (from the many subscriptions)
    at least an active one. The result should be in B14, B15, B16 I hope that it's a bit clearer now.




    ABCDEF
    1CustomerIDSubscription StartLengthSubscription EndActiveRemaining
    2101.01.201236531.12.2012FALSE-937
    3101.06.20159030.08.2015TRUE23
    4201.05.201336501.05.2014FALSE-456
    5201.01.201021030.07.2010FALSE-1807
    6201.01.20151011.01.2015FALSE-206
    7401.08.201136531.07.2012FALSE-1087
    8401.08.201540004.09.2016TRUE387
    9401.06.201290018.11.2014FALSE-259
    10
    11
    12
    13CustomerIDHas active subscriptions
    141TRUE / FALSE
    152TRUE / FALSE
    164TRUE / FALSE
  • Re: Worksheet Formula: Find value in an array


    Oh it's so easy! Thanks for the formula.
    And now I've learned something new.


    Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!