Find most match row #

[aqeelnokia99]

Hello i try to find most match row # in a array formula i have 2 sheet with same coloms name like i post down i make a formula like Po sheet 1 = all PO row in sheet 2 and qty sheet 1=all Qty in sheet 2 and its give me true and fales in array so i want to get the row # of whre most true like row 5
<style type="text/css"> table.tableizer-table { font-size: 12px; border: 1px solid #CCC ; font-family: Arial, Helvetica, sans-serif; } .tableizer-table td { padding: 4px; margin: 3px; border: 1px solid #CCC ; } .tableizer-table th { background-color: #104E8B ; color: #FFF ; font-weight: bold; }</style>

PO	qty	size	design	color	row
TRUE	FALSE	TRUE	FALSE	FALSE	1
FALSE	FALSE	FALSE	FALSE	FALSE	2
FALSE	FALSE	FALSE	FALSE	FALSE	3
TRUE	TRUE	FALSE	TRUE	FALSE	4
TRUE	TRUE	TRUE	TRUE	FALES	5

<thead>
</thead><tbody>
</tbody>

 

[Eric W]

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:

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:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	row	PO	virant	size	color	cf	design			qty		formula	PO	cf	size	virant	color	design	qty
2	1	a	3t	12x30	red	1	multi			9		3: cf virant color	a		12x30		green	mimi	9
3	2	b	3	12x40	yellow	2	uni			37
4	3	a	5d	12x30		3	mimi			9
5	4	c	2	12x45	blue	1	multi			62
6	5	a	12d	12x50	green	2	mimi			27
7	6	b	g4	12x32	dark	3	mimi			25
Sheet4

Cell Formulas
Range	Formula
L2	=matchmost($A$1:$J$7,$M$1:$S$1,M2:S2)

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

 

[aqeelnokia99]

formula is working very well thanks u very very much

 

[Eric W]

Glad to help.

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

 

[aqeelnokia99]

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