Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-27-2020, 06:37 PM
Cantora Cantora is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2019
Novice
Help required: Combining multiple matches in to a single cell
 
Join Date: Jan 2020
Posts: 6
Cantora is on a distinguished road
Default 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
Attached Files
File Type: xlsx Wine List.xlsx (34.0 KB, 11 views)

Last edited by Cantora; 01-28-2020 at 12:03 AM.
Reply With Quote
  #2  
Old 01-27-2020, 11:46 PM
xor xor is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

In B6: =IF(N6="",TEXTJOIN(", ",TRUE,$C6:$M6),"Other")
Reply With Quote
  #3  
Old 01-28-2020, 12:07 AM
Cantora Cantora is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2019
Novice
Help required: Combining multiple matches in to a single cell
 
Join Date: Jan 2020
Posts: 6
Cantora is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
In B6: =IF(N6="",TEXTJOIN(", ",TRUE,$C6:$M6),"Other")
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.
Reply With Quote
  #4  
Old 01-28-2020, 05:08 AM
xor xor is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Not obvious to me what you mean.
Reply With Quote
  #5  
Old 01-28-2020, 05:47 AM
Cantora Cantora is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2019
Novice
Help required: Combining multiple matches in to a single cell
 
Join Date: Jan 2020
Posts: 6
Cantora is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
Not obvious to me what you mean.
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.
Reply With Quote
  #6  
Old 01-28-2020, 07:27 AM
xor xor is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I withdraw. Maybe someone else can help.
Reply With Quote
  #7  
Old 01-28-2020, 07:46 AM
p45cal's Avatar
p45cal p45cal is offline Help required: Combining multiple matches in to a single cell Windows 10 Help required: Combining multiple matches in to a single cell Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.

Last edited by p45cal; 01-29-2020 at 04:43 AM. Reason: spelling
Reply With Quote
  #8  
Old 01-29-2020, 04:08 AM
ArviLaanemets ArviLaanemets is offline Help required: Combining multiple matches in to a single cell Windows 8 Help required: Combining multiple matches in to a single cell Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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.
Attached Files
File Type: xlsm Wine List.xlsm (28.5 KB, 5 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help required: Combining multiple matches in to a single cell Help with some VBA code Required dmcg9760 Excel Programming 9 03-01-2016 03:59 PM
Help required: Combining multiple matches in to a single cell 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: Combining multiple matches in to a single cell 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft