Help required: Combining multiple matches in to a single cell

p45cal · #1 01-28-2020, 07:46 AM

Try, in B2, array-entering this formula:

Code:

=TEXTJOIN(", ",TRUE,IF(MATCH(IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),0)=ROW(Varieties!$A$1:$A$19),IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""),""))

and copy down.

Not forgetting to add Rose to the list of Varieties on the Varieties sheet.

The simpler array-entered:

Code:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(Varieties!$A$2:$A$20,$A2)),Varieties!$B$2:$B$20,""))

works but because you sometimes have multiple finds (eg. in row 7 you have HA HA MARL PINOT NOIR where both Pinot and Noir are found so the result shows as Pinot Noir,Pinot Noir, the more complex formula eliminateas those.

I'll leave you to envelop that formula in an IF statement to show Other when none are found.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with some VBA code Required	dmcg9760	Excel Programming	9	03-01-2016 03:59 PM
What IF statement required	dr4ke	Excel	8	09-01-2011 07:41 AM
Formula Help Required	OTPM	Excel	6	08-31-2011 02:58 AM
Help required with spacing	rohanmalhotra	Word VBA	3	08-11-2011 04:06 AM
Suggestion required	domex	Word	0	10-06-2010 05:35 AM