Find most match row #

aqeelnokia99

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>
[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]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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>[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
 
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>








[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]
 
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
You must log in or register to reply here.

Similar threads

L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
509
Domenic
D
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
533
sspatriots
S
A
  • Solved
Changing recorded macro to have dynamic selection of cells when trying to format a sheet.
Replies
4
Views
850
Michael M
Michael M
E
  • Solved
VBA - Find match from another workbook, with multiple criterias, and paste in first awailable row
2
Replies
10
Views
935
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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