My question is very similar to How to concatenate values in multiple cells based on a condition? and I've had a lot of success applying Mourits de Beer's answer in the past; however for my current problem, I need to only join values that meet two sets of criteria instead of one.
I have Sheet_1 with ID numbers, PO numbers, and Text values that looks like this:
| ID | PO | CLASS |
|---|---|---|
| 1 | 223 | A |
| 1 | 334 | B |
| 2 | 334 | A |
| 3 | 556 | B |
| 3 | 445 | A |
| 1 | 445 | A |
| 3 | 334 | B |
I have a Sheet_2 with unique IDs where I need to join all unique PO numbers into a single cell based on whether they are Class A or B, like this: | ID | CLASS = A and ID = A2 | CLASS = B and ID = A2 | | ----- |---------------------- | ---------------------- | | 1 | 223, 445 | 334 | | 2 | 334 | N/A | | 3 | 445 | 556, 334 |
I tried to nest an AND() function inside of the IF function so the formula only works if BOTH conditions are met, but this isn't returning all true values.
=ARRAYFORMULA(TEXTJOIN(", ",TRUE,(UNIQUE(IF(AND(Sheet_1!$ID$2:$ID=$Sheet_2!ID2,'Sheet_1!$CLASS$2:$CLASS="A"),Sheet_1!$PO$2:$PO,"NA")))))
I need the function to work in Excel/Google Sheets and do not have strong VBA skills, but any help is appreciated! (This is also my first question here, so please be kind and let me know if there is anything I can do to explain/post my questions better next time. Thank you!)
