Hello,
Do you mean you are looking for the row in which you have the MAX of TRUE's ... ???
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
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
Hello,
Do you mean you are looking for the row in which you have the MAX of TRUE's ... ???
i have data like this
Answer is Row 3 because its have most match Result right hand data
row PO size color design qty formula PO size color design qty 1 a 12x30 red multi 9 Answer is Row 3 a 12x30 green mimi 9 2 b 12x40 yellow uni 37 3 a 12x40 green mimi 9 4 c 12x45 blue multi 62 5 a 12x50 green mimi 27 6 b 12x32 dark mimi 25
Try:
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
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell 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))}
Note: Do not try and enter the {} manually yourself
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
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")
I found it cumbersome to put both results in the same formula. Here are a few options:
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
Worksheet Formulas
Cell 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&" ",""))
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell 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))} N2 {=TEXTJOIN(",",1,IF(I2:M2<>INDEX($B$2:$F$7,$H$2,0),$I$1:$M$1,""))}
Note: Do not try and enter the {} manually yourself
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.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
Its Working Thanks u very very much
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 ?
row PO virant size color cf design qty formula PO cf size virant color design qty 1 a 3t 12x30 red 1 multi 9 3 a 12x30 green mimi 9 2 b 3 12x40 yellow 2 uni 37 3 a 5d 12x30 3 mimi 9 4 c 2 12x45 blue 1 multi 62 5 a 12d 12x50 green 2 mimi 27 6 b g4 12x32 dark 3 mimi 25
Last edited by aqeelnokia99; Oct 7th, 2019 at 02:36 PM.
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))}
Consider:
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
Worksheet Formulas
Cell 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&" ",""))
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula 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))}
Note: Do not try and enter the {} manually yourself
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.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
Like this thread? Share it with others