#1
|
|||
|
|||
Help required: Combining multiple matches in to a single cell
Hi,
I'm trying to provide a report that splits wine up in to it's varieties based on the bottle description. I want to: 1. Read the description (in column A) 2. looks through the table on sheet "Varietie", column A for matching varieties 3. Provide the combination of Varieties from sheet "Varieties", column B. The issue is that the wine might have multiple varieties in it - and so I want to search the description for key words and then return those key words in to a single cell. i've managed to do this by separating varieties in to different columns (C:M) and then using a simple vba script to combine them in to column B, but i'm wondering if there's a way to do this simpler via maybe just one or two formulas. In the attached example I've highlighted two lines that contain multiple varieties and combine these together. Any help is appreciated. This data attached is only for red wine for 1 month - but i'll have to do this for white wine and across a year - so the data will grow a lot and i'll end up needing dozens of different separate varieties Cheers! Dave Last edited by Cantora; 01-28-2020 at 12:03 AM. |
#2
|
|||
|
|||
In B6: =IF(N6="",TEXTJOIN(", ",TRUE,$C6:$M6),"Other")
|
#3
|
|||
|
|||
Hi, thanks so much for the response! unfortunately that doens't solve my problem, and i think that's maybe my bad for not explaining clearly. I've re-worded my request:
I want to: 1. Read the description (in column A) 2. looks through the table on sheet "Varietie", column A for matching varieties 3. Provide the combination of Varieties from sheet "Varieties", column B. The issue is that the wine might have multiple varieties in it - and so I want to search the description for key words and then return those key words in to a single cell. There will be dozens of different varieties and currently I'd have to create a formula for every single variety. |
#4
|
|||
|
|||
Not obvious to me what you mean.
|
#5
|
|||
|
|||
On my current sheet I have to use a formula to search the description of the wine for each variety individually and then stich them all together. -
E.G for a wine with the description "Cantora's delicious cab shiraz merlot", I need 3 formulas to search the descriptoin and return the results - so I end up with 3 results in their own individual columns: "Cab Sauv", "Shiraz", and "Merlot". I then need a formula to stitch the three results together. Is there a way to use just one formula to search the description for all the varieties at once? there will be dozens and dozens of wine varieties, and each variety can have several abreviations - so doing a formula for every single variety will be a big problem. |
#6
|
|||
|
|||
I withdraw. Maybe someone else can help.
|
#7
|
||||
|
||||
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,""),"")) 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,"")) I'll leave you to envelop that formula in an IF statement to show Other when none are found. Last edited by p45cal; 01-29-2020 at 04:43 AM. Reason: spelling |
#8
|
|||
|
|||
Check UDF in attached example!
In Varieties table, you must have all possible two-word varieties before single-word varieties listed - otherwise you may get them twice. |
Thread Tools | |
Display Modes | |
|
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 |