I am attempting to return corresponding/related order numbers for same customer and timeframe. The twist is that there are two categories, A and B. If looking at an A category, I am looking to return all B order numbers. If looking at a B category, looking to return all A order numbers (once again, for same customer and timeframe).
Note that Category A orders cover multiple days, whereas Category B orders cover one day..
Anyway, so far, I am using the following formula:
=IF(J2="A",TEXTJOIN(";",1,UNIQUE(FILTER($E$2:$E$55,(A2<=$A$2:$A$55)*(B2>=$B$2:$B$55)*(C2=$C$2:$C$55),""))),IF(J2="B",TEXTJOIN(";",1,UNIQUE(FILTER($E$2:$E$55,(A2>=$A$2:$A$55)*(B2<=$B$2:$B$55)*(C2=$C$2:$C$55),"")))))
But, I am getting extraneous order numbers. For example, if looking at Part A categories, it will return both Category A and B order numbers. If I attempt to modify to exclude a category, it does not return any results.
See attached spreadsheet. Column K is the formula result, Column L is the desired result.
Any ideas? Thanks.
Bookmarks