• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MATCH MISMATCH FORMULA

@SirJB7 (that's to say, myself)

Hi, me!

I wonder just this: How could a problem still be unsolved when the suggested solution wasn't downloaded and tested before?

I wonder, and wonder, and go on wondering... and I can't find out a reasonable answer.

Regards to same Hi'ed!
 
dont use that much Brains !!...JB sometimes it can lead to over confidence no hard feelings bro.. i has download the file!... when it was uploaded in docs.google.. UNDERSTOOD!!
 
awsomeeeeeeeeeeee.........kyle You really have Out Of the Box Thinking!!..

am impressed !..could you tell me the secrect to your knowledge bro!!
 
JB we are here to help one another! not to create a bad influence or taunt !..

sorry if you think my words are rude :)


kyle you earn my gratitude & respect... cause i really respect those who have vast treasure of knowledge and help ppl as well...

kyle do you have e-books or! how did you learn that much !...can you prove me with a guideline pls
 
KYLE how would i increase the Range... i actually need to check the data!... its based on around 4000 entries!...


if i keep Quantity cell empty so it results into "Container & Destination Mismatch"

and when i Keep Destination cell empty so it says "Container & Quantity Mismatch"

and when i keep only container no. and and two cells empty so it says "Container Mismatch"
 
All formulas? The range should increase automatically, assuming the data is put in columns A:C on Sheet2. The dynamic range may have become invalid somehow. Have you renamed any sheets or changed the structure? Do you still have the column headers on sheet 2? If not put those back in.
 
Hi, xcruc1at3r!

Give a look at the attached file: ttp://www.4shared.com/file/52v1Bac2/Copy_of_MATCH_and_MISMATCH__fo.html

It's the same original file uploaded by Kyle McGhee, just with the additions of two entries in Sheet2 and the related copy lines in Sheet1.

Regards!
 
well i have place the original data in it.... so you can check the errors out...

https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdFJkai1zSWNHR3dOczA3WlhKM1l6OEE

if you will search the Highlighted yellow container is in sheet 2 you would find it OK...but according to the kyle your method it says container mismatch...
 
Hi, xcruc1at3r!

I downloaded your file, added the named range "CompareTo" that Kyle McGhee defined in his uploaded solution (and that has been deleted in your workbook), and I think that it's now working again.

Check this: http://dl.dropbox.com/u/60558749/MATCH_and_MISMATCH%20%28for%20xcruc1at3r%20from%20Kyle%20McGhee%20at%20chandoo.org%29.xls

Regards!
 
as i see it The Kyle's method is checking Container No. Dest & QTY against ...but i would be other way around Should give Importance to Container 1st 2nd should be Dest and 3rd should be QTY...
 
Hi, xcruc1at3r!

Yes, row 9, it now shows Perfect Match in column E. Did you check it in the uploaded file I sent you? (same as yours with the addition of the missing named range stated before).

And about precedence order, Kyle McGhee's method gives major importance to Container first, Destination second, Quantity third.

If you need/want a different order, firstly you have to modify range J1:K9 indicating the mismatch precedence messages, for values associated to 1 thru 6 in column J.

Regards!
 
i am not able to download it cause of office policy restrictions, can only use Doc.google

well as i see it Kyle McGhee's method gives importance to Dest 1st then QTY and then Container no.!!! due to which the highlighted row was result was Container mismatch...and if you select the Container no. and search in sheet 2 so you find the container in sheet 2 with same info as in sheet 1... the result should have been perfect match but it was Container mismatch..
 
Hi, xcruc1at3r!

Here's uploaded to Google Docs:

https://docs.google.com/open?id=0B6HvMnuiLpy1a2Y4V0pHUEJRTmF6ekdyNnVfQzR1QQ

Please check as described in previous post, and eventually resend it properly modified, and with helper columns fulfilled if further explanations required.

Regards!
 
i have downloaded the File friend but still same scenario the data with MMUTL version result as Container Mismatch is not a correct result... if you search the Container No. in cell A106,A112.... in sheet 2 you will Find the Container in Sheet 2 as well so that means the Container is Not Mismatch and the Dest. against that container is no Mismatch but the QTY is mismatch...so the REsult should be QTY MISMATCH...

Sheet 1

APHU7126039 CMB 129

sheet 2

APHU7126039 CMB 119

as you can see that Container and Dest. are Match but QTY is mismatch...
 
Hi xcruc1at3r & SirJB7,


I apologize for my lack of response, as I have been quite busy. I see what you mean regarding the incorrect results. I think inverting the constant {1;2;4} to {4;2;1} will give you the results you want. Change the lookup table to this

[pre]
Code: 
0	No Match
4	Destination & Quantity Mismatch
2	Container & Quantity Mismatch
6	Quantity Mismatch
1	Container & Destination Mismatch
5	Destination Mismatch
3	Container Mismatch
7	Perfect Match
[/pre]
This will effectively give more weighting to the container field.
 
@Kyle McGhee

Hi!

There's something that still intrigues me: in the previous version, if you take row 106, the link in column G pointed to #Sheet2!$A$276, which corresponds to Container No. TCNU8356272 and not to APHU7126039. And that happened with many other rows...

I'm still wondering why.

Regards!

PS: Chapeau, monsieur! An excellent approach to face this issue.
 
SirJB that is because of the hyperlink is focused on the result B & C... not On A cause according to the formula the B & C are correct but the container is not correct due to which it the hyperlink reflected TCNU8356.......


Kyle : pls do not apologies i do understand that u must be having alot of responsibilities of your own and busy schedule...and Above of all you are trying to help me out and Same goes for SirJB...so pls pls Dont apologies...:)...am really very THankful to you Both for given be importance and trying to help me out...my utmost gratitude to you both :)


let me trying to change the sequence order...and would revert back...
 
SirJB7


The reason why those mismatches were occurring relates to the weighting that {1;2;4} gives each of the fields.


A container match = 1

A destination match = 2

A quantity match = 4


So if a row, such as the example provided, matches one row on the data tab with container and destination, MMULT returns 3; and if it also matches another row with destination and quantity, MMULT returns 6. The LARGE function will grab the 6, which is incorrect in this case. Inverting the {1;2;4} to {4;2;1} applies the heaviest weight to container. Now, in this same situation the Container/Destination match will return 6 and the Destination/Quantity match will return 3.
 
after changing the order...apply filter and check the NO match result row no. 589 & 736

kindly check the No Match result...basically Container and Dest. are matching but the Qty is not available as of data in row 701 APZU4180743 results ... why the result are not same when the condition is same ?
 
Hi xcruc1at3r & All,


Am late on this thread. Yesterday went through this thread especially through Kyle's posts. I have tried workout with a bit different approach. Here is the link:


http://dl.dropbox.com/u/60644346/MATCH_and_MISMATCH%20Version%2002.xls


@ Kyle: I will give you 10/10 for your hyperlink formula. Thumbs up for that.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top