Search code examples
excelvbaexcel-formulaworksheet-function

Excel Group and Match by Product Name

I have an excel spreadsheet of product data that I need to be reorganized and grouped by product name. An example of a product name looks like:

Bee 22"x36" Table Runner, Ecru

Is there a formula that can find and group all other products with the same name?

For example, I would need the formula to also include

Bee 22"x48" Table Runner, White

in the grouped results. It's the same product just a different color and size. I'm thinking I will need to use excel wildcards, but that's all I've got.

Additional Examples of Products:

Carillon Pendant Large Ivory
Carillon Pendant Medium Ivory
Carillon Pendant Small Ivory
Cashmere Fur Pillow Cream
Cashmere Fur Pillow Light Grey
Focus King Bed
Focus Nightstand
Focus Queen Bed
Focus Sideboard
Godenza Dining Table Rectangular Black Ash
Godenza Dining Table Rectangular Walnut
Godenza Dining Table Round Black Ash
Goldman Statue Large
Goldman Statue Small

Solution

  • I've assumed that the structure of a given string is something like: <manufacturer> <dimensions> <product>, <colour>.

    In other words, in the string Bee 22"x36" Table Runner, Ecru:

    • <manufacturer> = Bee
    • <dimensions> = 22"x36"
    • <product> = Table Runner
    • <colour> = Ecru

    I've also assumed that when you say you want to "group by product name", you want to group by <product> (i.e. the text between <dimensions> and <colour>).

    Lastly, I've assumed that it may be better to extract <product> first. Thereafter, you can sort/re-organise the rows however you want, since you don't describe in detail the end result you want to achieve.

    Using Excel functions and helper columns

    Although MATCH offers limited support for wildcards like * and ?, I've not used it. If you have a given string in cell A2, try the following:

    • in cell B2, paste: =INDEX(SEARCH({"0 ","1 ","2 ","3 ","4 ","5 ","6 ","7 ","8 ","9 "},SUBSTITUTE(A2,"""","")),MATCH(TRUE,ISNUMBER(SEARCH({"0 ","1 ","2 ","3 ","4 ","5 ","6 ","7 ","8 ","9 "},SUBSTITUTE(A2,"""",""))),0))
    • in cell C2, paste: =SEARCH(" ",MID(A2,1,9999),B2)+1
    • in cell D2, paste: =SEARCH(",",A2)
    • in cell E2, paste: =MID(A2,C2,D2-C2)

    This should give you the extracted <product> in cell E2.

    Non-VBA

    Using VBA and regular expressions

    Instead of the approach above, you could try using VBA (which lets you use regular expressions for more sophisticated/flexible string matching).

    1. Open VB Editor (Alt + F11)
    2. Insert > Module
    3. Paste code below in the newly inserted module.

    Code:

    Option Explicit

Public Function ExtractProduct(ByVal someText As String) As Variant

    Const PRODUCT_PATTERN As String = "\d+["" ]?x ?\d+""? (.+?),"

    Dim regExp As Object
    Set regExp = CreateObject("VBScript.RegExp")
    regExp.Pattern = PRODUCT_PATTERN

    Dim matchesFound As Object
    Set matchesFound = regExp.Execute(someText)

    If matchesFound.Count > 0 Then
        If matchesFound(0).SubMatches.Count > 0 Then
            ExtractProduct = matchesFound(0).SubMatches(0)
        Else
            ExtractProduct = CVErr(xlErrNA)
        End If
    Else
        ExtractProduct = CVErr(xlErrNA)
    End If
End Function
    1. Then you should be able to call ExtractProduct from the worksheet directly (i.e. assuming cell A2 contains some input, enter =ExtractProduct(A2) in cell B2)

    Unfortunately, built-in worksheet functions in Excel do not currently appear to support regular expressions (compared to something like Google Sheets). So this can't be achieved with just a simple formula -- or at least that's my understanding.