Find most match row #

aqeelnokia99

Board Regular
Joined
Dec 23, 2018
Messages
145
Office Version
  1. 2013
Platform
  1. Windows
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>
POqtysizedesigncolorrow
TRUEFALSETRUEFALSEFALSE1
FALSEFALSEFALSEFALSEFALSE2
FALSEFALSEFALSEFALSEFALSE3
TRUETRUEFALSETRUEFALSE4
TRUETRUETRUETRUEFALES5

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hello,

Do you mean you are looking for the row in which you have the MAX of TRUE's ... ???
 
Upvote 0
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>
rowPOsizecolordesignqty formulaPOsizecolordesignqty
1a12x30redmulti9 Answer is Row 3a12x30greenmimi9
2b12x40yellowuni37
3a12x40greenmimi9
4c12x45bluemulti62
5a12x50greenmimi27
6b12x32darkmimi25

<thead>
</thead><tbody>
</tbody>
Answer is Row 3 because its have most match Result right hand data
 
Upvote 0
Try:


Book1
ABCDEFGHIJKLM
1rowPOsizecolordesignqtyformulaPOsizecolordesignqty
21a12x30redmulti93a12x30greenmimi9
32b12x40yellowuni37
43a12x40greenmimi9
54c12x45bluemulti62
65a12x50greenmimi27
76b12x32darkmimi25
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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")
 
Upvote 0
I found it cumbersome to put both results in the same formula. Here are a few options:


Book1
ABCDEFGHIJKLMNO
1rowPOsizecolordesignqtyformulaPOsizecolordesignqty
21a12x30redmulti93a12x30greenmimi9sizesize
32b12x40yellowuni37 12x40
43a12x40greenmimi9
54c12x45bluemulti62
65a12x50greenmimi27
76b12x32darkmimi25
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
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>








rowPOvirantsizecolorcfdesign qty formulaPOcfsizevirantcolordesignqty
1a3t12x30red1multi 9 3a 12x30 greenmimi9
2b312x40yellow2uni 37
3a5d12x30 3mimi 9
4c212x45blue1multi 62
5a12d12x50green2mimi 27
6bg412x32dark3mimi 25

<thead>
</thead><tbody>
</tbody>
 
Last edited:
Upvote 0
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))}
 
Upvote 0
Consider:


Book1
ABCDEFGHIJKLMNOPQRST
1rowPOvirantsizecolorcfdesignqtyformulaPOcfsizevirantcolordesignqty
21a3t12x30red1multi93a12x30greenmimi9cf virant color
32b312x40yellow2uni373
43a5d12x303mimi9
54c212x45blue1multi62
65a12d12x50green2mimi27
76bg412x32dark3mimi25
Sheet4
Cell Formulas
RangeFormula
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top