Excel - if specific text shows up in column b, include all rows that have the text that shows up in column a of that row

Question

Large spreadsheet. There are multiple occurrences of the same business names in column A, and column B has various categories repeating randomly. I need to be able to either filter or copy all rows for business names (column A) that have one specific category show up at least once in column B. So even if the same business name shows up 5 times with 5 different categories listed in column B, and only one of the cells in column B contains the required category, I need ALL the lines for that business name to show up regardless of the other categories. I've been looking and looking and can't find this specific scenario. Thank you in advance!

Answer 1

In macro1, loop through each row in column B. If the value matches the category of interest, call macro2 and pass the business from column A as an argument.

In macro2, verify that the business name has not yet been copied (e.g., Find fails to find the name). Loop through each row in column A. If the name matches the passed in name, copy the row to the target.

Answer 2

Hi,

Try this formula:

=TEXTJOIN(",",TRUE,FILTER(B:B,A:A=D2,""))

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.