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 09, 2024

Thanks HansVogelaar - I have sent you a direct message with a scrubbed version.

HansVogelaar's avatar
HansVogelaar
MVP
Dec 09, 2024

It's this part:

            IF(TRUE,
                IF(Catalogue!$M$3:$M$987 = "Y", " UK", "") &
                IF(Catalogue!$O$3:$O$987 = "Y", " IT", "") &
                IF(Catalogue!$Q$3:$Q$987 = "Y", " DE", ""),
            ""),

In Catalogue row 243, both M243 and O243 are "Y", so you concatenate "UK" and "IT". You do not check if each of the columns next to it contain Q1'25

  • 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



         

Resources