Forum Discussion
Solved
Help with Excel formula
Hi everyone,
I have a formula in P2 that looks to see what is in F2. If F2 says "Full CPL", then put "Full" in P2. If it does not, then take the column header names from columns H through N and separate them with semi colons. This all works great.
=IF(F2="Full CPL", "Full", TEXTJOIN("; ", TRUE, FILTER($H$1:$N$1, $H2:$N2="TRUE", "")))
See Capture 1 attachment.
However, the business has asked me to not distribute 2XL, and provide 3XL in its place (don't worry this isn't real. I've just renamed my columns and values to protect my business's data). I need to adjust my formula so that if 2XL is TRUE, it will put 3XL into column P instead. And also, will only display 3XL once, not twice. You can see an example of what I would like it to look like in the Capture 2 attachment.
Is this possible?
Try this one:
=LET( swap, SWITCH(size, "2XL", "3XL", size), filtered, UNIQUE(FILTER(swap, E2:J2 = "true"), 1), TEXTJOIN(", ", 1, filtered) )
Try this one:
=LET( swap, SWITCH(size, "2XL", "3XL", size), filtered, UNIQUE(FILTER(swap, E2:J2 = "true"), 1), TEXTJOIN(", ", 1, filtered) )- Shoot I clicked the wrong button. Sorry, I did not mean to mark as best response as I am still testing.
Patrick it seems to be doing what I need, but how do I incorporate that into my existing formula? I need to make sure it still looks at F2 first. Thanks!For your sheet it'd be:
=LET( size, $H$1:$N$1, swap, SWITCH(size, "2XL", "3XL", size), filtered, UNIQUE(FILTER(swap, H2:N2 = "true"), 1), join, TEXTJOIN(", ", 1, filtered), IF(F2 = "Full CPL", "Full", join) )
