Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts

    How to index match MAX or MIN value of a partial text in a table

    Hi all,

    I am trying to find price ladders Min and Max prices from a list of products which contain the text in a cell using the Index match formula.
    Not sure it is the best formula for the use but basically, I have a list of product name in column A, and in a separate table I have a column with products which contain many variations of this main product with various prices which are in another column.

    I would like to have the Min value in the price column of all the products containing the text in column A.

    Ex: in table 1, A1= Belinda

    In my second table:
    A1:A200=
    Belinda Red
    Belinda Steel
    Belinda Short
    Arnold basic
    Arnold Gold
    etc...

    Tab2, B1:B200=
    10$
    20$
    12$
    3$
    5$
    etc...


    I would like to find Min value for anything that contains Belinda which would be: 10$
    And max value: 25$.

    I have tried few variations but can not find the right value.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Can you post a sample of your worksheet?

  3. #3
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you for your quick reply

    Here is the example on the screenshot and the actual file is attached:

    From the "after" tab I d like to search for anything that contains "belinda" in the "complete list tab" column E and report the MIN price value (tab "complete list" column H of all product descriptions containing the text in column B of the tab "after" if that make sense.

    Table 1.jpgTable 2.jpg
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Umit,

    You can use a User Defined Function (UDF) to do this. Place the following code in a standard module.

    In a Standard Module:
    Code:
    Public Function LowPrice(rng As Range) As Double
    Dim cell As Range, cost(), index As Integer
    index = 1
    With Worksheets("complete pricelist")
    For Each cell In Range("Description")
        If InStr(1, cell, rng, vbTextCompare) > 0 Then
            ReDim Preserve cost(index)
            cost(index) = .Cells(cell.Row, "H")
            index = index + 1
        End If
    Next cell
    If index = 1 Then
        LowPrice = 0
    Else:
        LowPrice = WorksheetFunction.Min(cost())
    End If
    End With
    End Function
    
    
    Public Function HighPrice(rng As Range) As Double
    Dim cell As Range, cost(), index As Integer
    index = 1
    With Worksheets("complete pricelist")
    For Each cell In Range("Description")
        If InStr(1, cell, rng, vbTextCompare) > 0 Then
            ReDim Preserve cost(index)
            cost(index) = .Cells(cell.Row, "H")
            index = index + 1
        End If
    Next cell
    If index = 1 Then
        HighPrice = 0
    Else:
        HighPrice = WorksheetFunction.Max(cost())
    End If
    End With
    End Function
    On the After sheet in cell E4, place the following formula: =LowPrice(B4) then copy down

    On the After sheet in cell F4, place the following formula: =HighPrice(B4) then copy down

    The UDF will look for the value in cell B4 within each cell the Description column E on the complete pricelist sheet. If matches are found then the code assigns the corresponding prices in col H to an array variable to which it obtains the min and max values. Of no matches are found, it returns 0 for the min and max values ("-" with the accounting format you have applied)

    HTH,
    Maud

    umit1.png
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Umit (2015-08-08)

  6. #5
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud,

    Thank you it works on the sample but I just have an issue when I double click in the product name cell, it generates an error:

    name error.jpg

    And when I copied the code in the original spreadsheet it doesn't work neither. I must admit I have never used UDF or vba before, not sure If I have done something wrong. Here are the screenshots of the VBA and the original xls file error:

    VBA.jpg

    Original spreadsheet.jpg

    I can not attach the original file as it is too heavy for the forum but I can send it be email if that's ok for you (2Mo).

    Many thanks

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Umit,

    My bad! I forgot to mention that I created a named range called Description which is on the complete pricelist sheet E4:E6365. The code uses Description to check for a matching value. Please create that named range. Everything you did looks right.

    Maud

    Umit2.png
    Last edited by Maudibe; 2015-08-08 at 11:09.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Umit (2015-08-08)

  9. #7
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maud, It works perfectly!

    Thank you soo much!

    Umit

  10. #8
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Similar function with multiple text sources + NB and SUM

    Hi Maud,

    Your last post has been really helpful.

    I do not want to be asking too much but I think you can help me with this one which is slightly more complicated and again I can not find standard excel functions to solve it but it requires a similar method

    What I need to do is:

    On the example below,

    1) In the column J, I would like Excel to count the number of cells which contain both the value in the column B and the one in the cell I3 in the named range called Description.

    2) In the column H, I would like to have the sum of the prices of cells which contain both the value in the column B and the one in the cell I3 in the named range called Description.

    Example.png

    I think it is 2 different functions.
    Attached Files Attached Files

  11. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Not a problem at all. Could you please clarify which sheet(s) each of the columns and cell I3 refer to?

    Thanks,
    Maud

  12. The Following User Says Thank You to Maudibe For This Useful Post:

    Umit (2015-08-11)

  13. #10
    New Lounger
    Join Date
    Aug 2015
    Posts
    6
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Apologies my bad, I meant G3.

    Sheet "after" Cell G4: I would like to have the sum of the column H of sheet "Complete pricelist" for all the cells in column E of the sheet "complete pricellist" which containt both the text in cell B4 and G3 of the sheet "after".

    I previously created the named range "Description" for the column E of the sheet "complete pricelist" for the previous function.

    In the cell H4 of the sheet "after" I would like to count the number of cells in the column E of the sheet "complete pricelist" which containt both the text in G3 and B4 of the sheet "after" so that I can eventually have the average cost in I4 (sheet "after") by dividing the value in G4 by the number of cells in H4.

    Then I would like to copy and paste this formula for all the other columns (J,K,M,N) in sheet "after".

    I have tried to do it with NB.IF and SUM.IF functions but I would have to manually write both text in each cell and I have over 1000 cells by sheet and 10 sheets . Example below:

    text match for sum and nb.jpgtext match for sum and nb2.jpg

    I hope it makes sense.

  14. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Umit,

    In cell G4 place the formula =FMethod(B4) and copy down
    In cell H4 place the formula =Sku(B4) and copy down

    Function FMethod will sum all the cells in the H column (Complete pricelist sheet) where:
    1. The parent name value and the word "base plate" from the After sheet are both found in the corresponding values in the Description Column

    Function Sku will do the same but only count them

    Both methods are versatile so they can be used in the repeating pairs of columns. Keep in mind that there are more efficient methods to achieve the same results.

    HTH,
    Maud

    Code:
    Public Function FMethod(rng As Range) As Currency
    Dim cell As Range, cost As Currency, col As Integer
    col = Application.Caller.Column
    cost = 0
    With Worksheets("complete pricelist")
    For Each cell In Range("Description")
        If InStr(1, cell, rng, vbTextCompare) > 0 And _
          InStr(1, cell, Cells(3, col), vbTextCompare) > 0 Then
            cost = cost + .Cells(cell.Row, "H")
        End If
    Next cell
    BasePlate = cost
    End With
    End Function
    
    
    Public Function Sku(rng As Range) As Long
    Dim cell As Range, count As Long, index As Integer
    col = Application.Caller.Column
    count = 0
    With Worksheets("complete pricelist")
    For Each cell In Range("Description")
        If InStr(1, cell, rng, vbTextCompare) > 0 And _
          InStr(1, cell, Cells(3, col), vbTextCompare) > 0 Then
            count = count + 1
        End If
    Next cell
    Sku = count
    End With
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •