Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Find most match row #
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Find most match row #

    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

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,587
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Find most match row #

    Hello,

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

  3. #3
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    i have data like this
    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
    Answer is Row 3 because its have most match Result right hand data

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,731
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find most match row #

    Try:

    ABCDEFGHIJKLM
    1rowPOsizecolordesignqtyformulaPOsizecolordesignqty
    21a12x30redmulti93a12x30greenmimi9
    32b12x40yellowuni37
    43a12x40greenmimi9
    54c12x45bluemulti62
    65a12x50greenmimi27
    76b12x32darkmimi25

    Sheet2



    Array Formulas
    CellFormula
    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))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    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

  5. #5
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    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")

  6. #6
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,731
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find most match row #

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

    ABCDEFGHIJKLMNO
    1rowPOsizecolordesignqtyformulaPOsizecolordesignqty
    21a12x30redmulti93a12x30greenmimi9sizesize
    32b12x40yellowuni3712x40
    43a12x40greenmimi9
    54c12x45bluemulti62
    65a12x50greenmimi27
    76b12x32darkmimi25

    Sheet2



    Worksheet Formulas
    CellFormula
    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
    CellFormula
    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,""))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    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

  7. #7
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    Its Working Thanks u very very much

  8. #8
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    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.

  9. #9
    Board Regular
    Join Date
    Dec 2018
    Posts
    65
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find most match row #

    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))}

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,731
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find most match row #

    Consider:

    ABCDEFGHIJKLMNOPQRST
    1rowPOvirantsizecolorcfdesignqtyformulaPOcfsizevirantcolordesignqty
    21a3t12x30red1multi93a12x30greenmimi9cf virant color
    32b312x40yellow2uni373
    43a5d12x303mimi9
    54c212x45blue1multi62
    65a12d12x50green2mimi27
    76bg412x32dark3mimi25

    Sheet4



    Worksheet Formulas
    CellFormula
    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
    CellFormula
    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))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •