Forum Discussion

sruk89's avatar
sruk89
Brass Contributor
Dec 06, 2024

** Help ** Need to filter same result but in different formulas dependent on Quarter

Hi  HansVogelaar  I need help modify my formula/logic (request below) Formula 1 =TEXTJOIN("; ", TRUE,     FILTER(         Catalogue!$B$3:$B$987 &         IF((Catalogue!$L$3:$L$987 = E$1 & "...
excel
Formulas and Functions
sruk89's avatar
sruk89
Brass Contributor
Dec 10, 2024

thanks HansVogelaar - could you help me with this? As this is the part that im struggling with

HansVogelaar's avatar
HansVogelaar
MVP
Dec 10, 2024

Please explain the logic. If Concat!B1 is "XXX Group", should we look at columns K and L on the Catalogue sheet because K1 is "XXX Group"? Or should we look at more columns? If so, which ones, and how would that work?

  • sruk89's avatar
    sruk89
    Brass Contributor
    Dec 10, 2024

    Ok sure, the logic should only apply when XXX Group is chosen. The idea is that if there isn't a Y a or Quarter in M:N, O:P, Q:R then the assumption is that all these territories are getting said item at the same time.

    However lets say K103 has a Y and L103 has Q1'25 but M103 has a Y but N103 has Q2'25 then we know XXX UK & ROI are receiving said item later than XXX IT and XXX DACH.

    Another real example would be row 266 K = Y and L = Q1'25, M:N are blank so doesnt apply to this territory however we can see O266 = Y and P =Q2'25 so the IT marker should appear in the Q2 formula however we can see that Q266 = Y and R266 = Q1'25 so the DE marker should appear but only in the Q1 formula



     

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Dec 10, 2024

      I'm sorry, I cannot get my head around this. Anyone else?

      • sruk89's avatar
        sruk89
        Brass Contributor
        Dec 10, 2024

        hopefully, this is clearer

        1.)
        B243 = Test - do not remove
        M243 = Y (UK)
        N243 = Q1'25
        O243 = Y (IT)
        P243 = Q2'25

        Id expect to returned in formula 1 "Test - do not remove UK" as this formula is looking for Q1'25 values in formula 2 id expect to see "Test - do not remove IT" as this is looking for Q2'25 values




        with a simplified formula

        =TEXTJOIN("; ", TRUE,

            FILTER(

                Catalogue!$B$3:$B$987 &

                IF(Catalogue!$N$3:$N$987 = E$1, " UK", "") &

                IF(Catalogue!$P$3:$P$987 = E$1, " IT", ""),

                (Catalogue!$A$3:$A$987 = $D$1) *

                (Catalogue!$C$3:$C$987 = $D2)

            ),

            "None"

        )

Resources