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>
[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]PO[/TH]
[TH]qty[/TH]
[TH]size[/TH]
[TH]design[/TH]
[TH]color[/TH]
[TH]row[/TH]
[/TR]
</thead><tbody> [TR]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]TRUE[/TD]
[TD]FALSE[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]TRUE[/TD]
[TD]FALES[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Do you mean you are looking for the row in which you have the MAX of TRUE's ... ???

 

[aqeelnokia99]

i have data like this
<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>[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]row[/TH]
[TH]PO[/TH]
[TH]size[/TH]
[TH]color[/TH]
[TH]design[/TH]
[TH]qty[/TH]
[TH] [/TH]
[TH]formula[/TH]
[TH]PO[/TH]
[TH]size[/TH]
[TH]color[/TH]
[TH]design[/TH]
[TH]qty[/TH]
[/TR]
</thead><tbody> [TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]12x30[/TD]
[TD]red[/TD]
[TD]multi[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD]Answer is Row 3[/TD]
[TD]a[/TD]
[TD]12x30[/TD]
[TD]green[/TD]
[TD]mimi[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]12x40[/TD]
[TD]yellow[/TD]
[TD]uni[/TD]
[TD]37[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]12x40[/TD]
[TD]green[/TD]
[TD]mimi[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[TD]12x45[/TD]
[TD]blue[/TD]
[TD]multi[/TD]
[TD]62[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[TD]12x50[/TD]
[TD]green[/TD]
[TD]mimi[/TD]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]b[/TD]
[TD]12x32[/TD]
[TD]dark[/TD]
[TD]mimi[/TD]
[TD]25[/TD]
[TD] [/TD]
[TD]
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Answer is Row 3 because its have most match Result right hand data

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	row	PO	size	color	design	qty		formula	PO	size	color	design	qty
2	1	a	12x30	red	multi	9		3	a	12x30	green	mimi	9
3	2	b	12x40	yellow	uni	37
4	3	a	12x40	green	mimi	9
5	4	c	12x45	blue	multi	62
6	5	a	12x50	green	mimi	27
7	6	b	12x32	dark	mimi	25
Sheet2

Cell Formulas
Range	Formula
H2	{=INDEX($A$2:$A$7,MATCH(MAX(MMULT(--(B2:F7=I2:M2),{1;1;1;1;1})),MMULT(--(B2:F7=I2:M2),{1;1;1;1;1}),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[aqeelnokia99]

Thanks u very much now i have Most match Row #that is 3 now i want what is not match in row 3 in my case in row 3 size not match so i want Result (3 "Size")

 

[Eric W]

I found it cumbersome to put both results in the same formula. Here are a few options:

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

Cell Formulas
Range	Formula
I3	=IF(I2<>INDEX(B$2:B$7,$H$2),INDEX(B$2:B$7,$H$2),"")
O2	=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&" ",""))
H2	{=INDEX($A$2:$A$7,MATCH(MAX(MMULT(--(B2:F7=I2:M2),{1;1;1;1;1})),MMULT(--(B2:F7=I2:M2),{1;1;1;1;1}),0))}
N2	{=TEXTJOIN(",",1,IF(I2:M2<>INDEX($B$2:$F$7,$H$2,0),$I$1:$M$1,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Put the I3 formula in and drag to the right. Any values not matching will be displayed.

If you have the TEXTJOIN function, you can use the N2 formula to list the headers (since there could be more than on mismatch) of the mismatches. If you do not have the TEXTJOIN function, then the longer O2 formula should work.

Hope something here works for you.

 

[aqeelnokia99]

Its Working Thanks u very very much

 

[aqeelnokia99]

hi i have 1 more issue that my Data colom and and formula sheet colom not in like that i post its like 30 coloms and i verify about 10 colom thats are not on togather is this formula work on ?
<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>








[TABLE="class: tableizer-table"]
<thead>[TR="class: tableizer-firstrow"]
[TH]row[/TH]
[TH]PO[/TH]
[TH]virant[/TH]
[TH]size[/TH]
[TH]color[/TH]
[TH]cf[/TH]
[TH]design[/TH]
[TH] [/TH]
[TH] [/TH]
[TH]qty[/TH]
[TH] [/TH]
[TH]formula[/TH]
[TH]PO[/TH]
[TH]cf[/TH]
[TH]size[/TH]
[TH]virant[/TH]
[TH]color[/TH]
[TH]design[/TH]
[TH]qty[/TH]
[/TR]
</thead><tbody> [TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]3t[/TD]
[TD]12x30[/TD]
[TD]red[/TD]
[TD]1[/TD]
[TD]multi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]12x30[/TD]
[TD] [/TD]
[TD]green[/TD]
[TD]mimi[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]3[/TD]
[TD]12x40[/TD]
[TD]yellow[/TD]
[TD]2[/TD]
[TD]uni[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]37[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]5d[/TD]
[TD]12x30[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]mimi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[TD]2[/TD]
[TD]12x45[/TD]
[TD]blue[/TD]
[TD]1[/TD]
[TD]multi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]62[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[TD]12d[/TD]
[TD]12x50[/TD]
[TD]green[/TD]
[TD]2[/TD]
[TD]mimi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]b[/TD]
[TD]g4[/TD]
[TD]12x32[/TD]
[TD]dark[/TD]
[TD]3[/TD]
[TD]mimi[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[aqeelnokia99]

ithink its work easy =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&" ",""))
how to set this one ({=INDEX($A$2:$A$7,MATCH(MAX(MMULT(--(B2:F7=I2:M2),{1;1;1;1;1})),MMULT(--(B2:F7=I2:M2),{1;1;1;1;1}),0))}

 

[Eric W]

Consider:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
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	a		12x30		green	mimi	9	cf virant color
3	2	b	3	12x40	yellow	2	uni			37		3
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
T2	=TRIM(IF(M2<>INDEX(B2:B7,$L$2),M$1&" ","")&IF(N2<>INDEX(F2:F7,$L$2),N$1&" ","")&IF(O2<>INDEX(D2:D7,$L$2),O$1&" ","")&IF(P2<>INDEX(C2:C7,$L$2),P$1&" ","")&IF(Q2<>INDEX(E2:E7,$L$2),Q$1&" ","")&IF(R2<>INDEX(G2:G7,$L$2),R$1&" ","")&IF(S2<>INDEX(J2:J7,$L$2),S$1&" ",""))
L2	{=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))}
L3	{=INDEX($A$2:$A$7,MATCH(MAX(MMULT(COUNTIFS(OFFSET($B$2:$J$2,ROW($B$2:$B$7)-ROW($B$2),0),M2:S2,$B$1:$J$1,$M$1:$S$1),TRANSPOSE(COLUMN($M$1:$S$1)^0))),MMULT(COUNTIFS(OFFSET($B$2:$J$2,ROW($B$2:$B$7)-ROW($B$2),0),M2:S2,$B$1:$J$1,$M$1:$S$1),TRANSPOSE(COLUMN($M$1:$S$1)^0)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

When you don't have the same number of columns, and/or they're not in the same order, it's much trickier. The "easy" way is in L2. You have to manually include a condition like (M2=$B$2:$B$7) for each column you want to compare, in 2 places. You just have to make sure that you compare the right columns in each place.

The "complicated" way is in L3. You don't have to individually identify each column. It uses the headings in M1:S1 and finds the equivalent columns in B1:J1. The L2 formula is shorter for comparing 7 columns, but I think just 1 more comparison would make it longer.

The T2 formula displays what columns don't match. You have to match the right columns manually though. I don't see a way to simplify it without TEXTJOIN, or VBA.