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

1. ## 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.  Reply With Quote

2. ## Re: Find most match row #

formula is working very well thanks u very very much   Reply With Quote

3. ## Re: Find most match row #

Glad to help. Out of curiosity, which formula did you end up using?  Reply With Quote

4. ## 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  Reply With Quote

## User Tag List

.tableizer-table, 1px, border, find, formula 