Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Find most match row #
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,720
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find most match row #

    Speaking of VBA, it might be worth using a UDF (User-Defined Function). If you want to try that, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to insert a module. Paste the following code in the window that opens:

    Code:
    Public Function MatchMost(ByVal range1 As Range, ByVal range2 As Range, ByVal range3 As Range)
    Dim i As Long, j As Long, MyData As Variant, hdrs1 As Variant, vals As Variant, c(1 To 100) As Variant
    Dim m As Long, m2 As Long, n As Long
    
    
        MyData = range1.Value
        hdrs1 = range2.Value
        vals = range3.Value
        
        For i = 1 To UBound(hdrs1, 2)
            c(i) = WorksheetFunction.Match(hdrs1(1, i), WorksheetFunction.Index(MyData, 1, 0), 0)
        Next i
        
        m = -1
        m2 = 0
        
        For i = 2 To UBound(MyData)
            n = 0
            For j = 1 To UBound(hdrs1, 2)
                If MyData(i, c(j)) = vals(1, j) Then n = n + 1
            Next j
            If n > m Then
                m2 = i
                m = n
            End If
        Next i
        
        MatchMost = MyData(m2, 1) & ": "
        For i = 1 To UBound(hdrs1, 2)
            If MyData(m2, c(i)) <> vals(1, i) Then MatchMost = MatchMost & hdrs1(1, i) & " "
        Next i
            
    End Function
    Press Alt-Q to close the editor. Now enter this formula on your sheet:

    ABCDEFGHIJKLMNOPQRS
    1rowPOvirantsizecolorcfdesignqtyformulaPOcfsizevirantcolordesignqty
    21a3t12x30red1multi93: cf virant color a12x30greenmimi9
    32b312x40yellow2uni37
    43a5d12x303mimi9
    54c212x45blue1multi62
    65a12d12x50green2mimi27
    76bg412x32dark3mimi25

    Sheet4



    Worksheet Formulas
    CellFormula
    L2=matchmost($A$1:$J$7,$M$1:$S$1,M2:S2)



    The formula is much simpler, if you don't mind the VBA.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  2. #12
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    formula is working very well thanks u very very much

  3. #13
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,720
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find most match row #

    Glad to help.

    Out of curiosity, which formula did you end up using?
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  4. #14
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    i using =TRIM(IF(I2<>INDEX(B2:B7,$H$2),I$1&" ","")&IF(J2<>INDEX(C2:C7,$H$2),J$1&" ","")&IF(K2<>INDEX(D2:D7,$H$2),K$1&" ","")&IF(L2<>INDEX(E2:E7,$H$2),L$1&" ","")&IF(M2<>INDEX(F2:F7,$H$2),M$1&" ",""))
    and
    {=INDEX($A$2:$A$7,MATCH(MAX((M2=$B$2:$B$7)+(N2=$F$2:$F$7)+(O2=$D$2:$D$7)+(P2=$C$2:$C$7)+(Q2=$E$2:$E$7)+(R2=$G$2:$G$7)+(S2=$J$2:$J$7)),(M2=$B$2:$B$7)+(N2=$F$2:$F$7)+(O2=$D$2:$D$7)+(P2=$C$2:$C$7)+(Q2=$E$2:$E$7)+(R2=$G$2:$G$7)+(S2=$J$2:$J$7),0))}
    its work very well

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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