+ Reply to Thread
Results 1 to 35 of 35

Comparing data between two worksheet , based on multiple conditions

  1. #1
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Comparing data between two worksheet , based on multiple conditions

    Hello guys.

    here i got two worksheets. By comparing data between those two worksheets , i want to solve logic function and mark the result with "yes" or "No" along column H (sheet1). The "yes'" result can be obtained through multiple cases (OR). But in every cases, it must fulfill both condition A and condition B (AND).


    So for the example, let say we plug in the formula in H18 :

    case1
    if column D18 (sheet1) = "Xtpaint" or " paint" or "whtpaint" ,
    condition A - A10(sheet1) = A:A (sheet2)
    condition B - Let say A18(sheet1) above is equal to A250(sheet2) ,then if B250 = "WDS"
    Result - H18="Yes"


    case2
    if column D18 (Sheet1) = "qc"
    condition A - A10(sheet1) = A:A (sheet2)
    condition B - Let say A18(sheet1) is equal to A100(sheet2) ,then if B100 = "coating"
    Result - H18= "Yes"


    case3
    if column D18 (Sheet1) = "W/H"
    condition A - A10(sheet1) = A:A (sheet2)
    condition B - Let say A18(sheet1) is equal to A333(sheet2) ,then if B333 = "Airsheet"
    Result - H18= "Yes"



    I try to use basic function like IF,OR,AND, but it be even complicated. So i guess macro can make it simple, but to be honest i dont know much about macro. Besides, list in Sheet1 and Sheet2 will be constantly updated and added up everyday, so guess the formula should be more flexible about the range. You may refer to my attachment to clearly see how i expect them to be.

    Thanks in advance. Appreciate your help.
    Attached Files
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Quote Originally Posted by pyol17 View Post
    case1
    if column D18 (sheet1) = "Xtpaint" or " paint" or "whtpaint" ,
    condition A - A10(sheet1) = A:A (sheet2)
    condition B - Let say A18(sheet1) above is equal to A250(sheet2) ,then if B250 = "WDS"
    Result - H18="Yes"
    Is that correct?
    I changed it to "painting" to meet the result.
    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    You should mark this thread as "Solved".

  4. #4
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Thanks for the code.

    It is fine, but only the part that "xpaint" , "whtpaint" , "qc" and some other text signature are case sensitive. I prefer them to be more free, so i dont i need to capitalize some letters in case i forgot. Can we change this any way?

    Note your reminder. Done marking it as "solved".

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Add one line at the top of the module
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Thanks dude..

  7. #7
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Dear Jindon.

    I just realized that this code miss to identify certain data, even they are supposed to be 'true'. I have checked it for several times, and i found that the code works perfectly fine on most of data, but just miss certain of them even the condition is true, which make me very confuse now.

    You may refer to attachment. I have marked it with red highlight to indicate miss identified data.


    defining error.xlsm

    I hope u can give a look after such this long time. Thanks.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Now you have changed that single "Prod. Order" have multiple "Document type".

    Can you clearly define what should be done in detail?

  9. #9
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    yup..one Production Order can have multiple document type..sorry for the miss info.I dont know this would be an issue.


    There is no extra requirement that i need, other than conditions specified above. I just want the code to identify "yes' and "no" more accurately, including the prod order that has multiple document type.(ex. Coating, painting and airsheet at one time )

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    You never have "painting","coating" in Col.C of "Submitted" sheet this time.

    So you should define all the process from the scratch.

  11. #11
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    So here is the "true" cases, applied to H18:H on sheet "checklist2"


    If column D (sheet1) = "Xtpaint"
    condition A) - A10("checklist2") = A:A ("Submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "Painting"

    If column D (sheet1) = "whpaint"
    condition A) - A10("checklist2") = A:A ("submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "Painting"

    If column D (sheet1) = "paint"
    condition A) - A10("checklist2") = A:A ("submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "Painting"


    If column D (sheet1) = "QC"
    condition A) - A10("checklist2") = A:A ("submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "coating"

    If column D (sheet1) = "W/H"
    condition A) - A10("checklist2") = A:A ("submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "Airsheet"


    Column C on "submitted" sheet can have several type of document such as
    1) painting
    2) coating
    3) airsheet
    4) MPI
    5) LPI
    6) WDS

    But the only "true" cases is only those which mentioned above. Same production order may have several type of document (column C sheet "submitted" ) at one time, painting and coating for example. So does production order (column A on "checklist2" sheet) column D on "checklist" sheet can have several workcounter such as "W/H",XTPAINT and "QC" at one time.


    As long as both the comparing of production order on "Checklist2" and "Submitted" are true, and at the same time their workcounter is matching with type of document, the case will be considered as true.


    In short, same production order between those 2 sheet ,that fulfill condition A and B on any 5 cases,they all can be true and marked as 'yes'. And i prefer column D on "chechlist2" and column C on "submitted" to be not case sensitive.

    comparison between 2 sheets.xlsm

    Thanks for entertaining my request.
    Last edited by pyol17; 08-27-2012 at 10:59 PM.

  12. #12
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    actually there is nothing wrong with your previous code. It works on most data by identifying the true cases on both condition A and B .

    But for some isolated cases, if u pay attention the red highlighted rows on sheet "checklist" , this row fail to be recofgnized even they fulfill both condition A and B. So if u dont want to start it from scratch, maybe u can fix only this part.
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    So you mean "painting report", "coating report" in col.C of "Submitted" should be ignored?

    Then my code results all the row with "No".

  14. #14
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    yes..they should be "painting" and "coating" actually. never mind.just ignore. i will fix this later.

    I just discover the problem with the previous code.When there is redundant Production Order but with different document type like "painting" and "coating" in sheet "submitted", the code cannot identify them as "yes" even both of them can be true. This is the part that i think should be fix.

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Since your data set and logic is totally different from your original data set, now it should compare:

    1) Col.D
    2) if 1) met, compare Col.C
    3) Col.C has Col.A

    You can't just simply modify the code. It is more like One to One relation to Many To Many relation now.

  16. #16
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Sorry.Confusingly i dont know how complicated this could be. But basically,here are the steps how this thing should work:



    1) Compare column A (checklist2) with column A:A (submitted). Does production order is similar, or fail the 1st condition already ? (condition A)

    2) If yes, refer to column D (checklist2),The workcounter can be "W/H" or " XTPAINT" or "PAINT"

    3) If column D (checklist2) = "W/H", does column C ("submitted") = "Airsheet"? (condition B)
    or if column D (checklist2) = "XTPAINT" , does column C ("Submitted") = " painting" ?

    You may refer to attachment. i have simplified those steps.
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Absolutely no.

    Now the key should be Col.C.
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Try the code already. Still doesnt work. My expected result should produce all "yes" on column H.



    At first the prod. order between "submitted" and "checklist" must be same , and like u said the key should be Column C "submitted". But it depends on what workcounter it refers to (column D "checklist2") .If :

    1) workcounter= XTPAINT / WHPAINT / PAINT , then column c "submitted" must be "painting" in order to get "yes" result.

    2) workcounter = QC , then column c "submitted" must be "coating" in order to get "yes" result.

    3) workcounter = W/H , then column c "submitted" must be "airsheet" in order to get "yes" result.
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    You hilited the line of

    112445603 2000 WAREHOUSE TO ISSUE MATERIALS W/H 00000000 2012/8/7 2012/8/7 No

    Why is it not "No"?

  20. #20
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    First i copy all the Prod order from "Checklist" to "submitted".. So this duplicate will fulfill all condition A.

    Then, i assign every workcounter to its correct document type.

    W/H = Airsheet
    XTPAINT / PAINT / WHPAINT = painting
    QC= coating

    So, all prod order will met the condition B. So to check whether your code works perfectly , all col. H at "checklist" will result to "YES".

    Thats why including 112445603 2000 WAREHOUSE TO ISSUE MATERIALS W/H 00000000 2012/8/7 2012/8/7 No should make "yes" instead.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Which column in "Checklist" and "Submitted" are you talking about?

    You said,
    If column D (sheet1) = "Xtpaint"
    condition A) - A10("checklist2") = A:A ("Submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250(sheet2) ,then if B250 must be = "Painting"
    You don't have "painting" in Col.B of "Submitted", all you have is just numbers.

  22. #22
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Sorry my bad.. It should be Column C "submitted" actually...

    If column D (sheet1) = "Xtpaint"
    condition A) - A10("checklist2") = A:A ("Submitted")
    condition B) - Let say A18("checklist2" ) above is equal to A250("submitted") ,then C250 ("submitted") must be "painting"
    Last edited by pyol17; 08-30-2012 at 12:02 AM.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Let's talk based on the your file in #7, "defining error.xlsm"

    1st record


    112432677 that is in row 40 and suppose to be "Yes".
    You have same number in A183, A801 and A2370 in "Sumitted" and the have
    Coating report
    Coating report
    Painting
    respectively, and none has Coating.

    Why Yes?

  24. #24
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Okey.lets focus on Prod Order 112432677.From both sheet, there is duplicates

    Sheet "Checklist2"
    Row#40 Col.D = QC
    Row#321 Col.D = QC
    Row#610 Col.D = XTPAINT
    Row#615 Col.D = XTPAINT
    Row#796 Col.D = QC


    Sheet "submitted"
    Row#183 Coating Report
    Row#801 Coating
    Row#2370 Painting


    I agree that row#183 "submitted" is a mistake. It should be "coating" instead, and i have change it but still nothing happen.

    But Row#40 "Checklist2" still should be able to detect Row#801 "submitted". Then it still fulfill both condition and resulting "Yes". So does Row#321,610,615,796.They all should be "yes" respectively.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    My latest code.

    Loop through Col.D of Sheet1,
    If find anything like "xtpaint", "whpaint", "paint", "qc", "W/H"
    Check Col.C of Sheet1 with Col.C of Sheet2
    If the they match, check if it has same product number.

    Is something wrong with this?

  26. #26
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    maybe in term of sequence.

    First, check if they have same product number between sheets. If no similarity, no further action will be taken. Total result will be considered as "No"

    But if the code find match product order betwen sheets, loop through Col.D of "checklist". And it will find either "XTPAINT/ PAINT / QC / WHPAINT / W/H " .
    Then Check Col.C of Sheet2 (submitted) either coating, painting ,or airsheet respectively. In order to fulfill condition B and resulting "YES"

    workcounter "XTPAINT/ PAINT / WHPAINT " should match with "painting" in Col.C sheet2.
    workcounter "QC" should match with "coating" in Col.C sheet2.
    workcounter "W/H" should match with "airsheet" in Col.C sheet2.


    Sorry for unable to explain it well.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Ahhh,,, I missed soemthing.

    Try this and see if this works.
    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    which workbook u refer now? i dont find them.

    But "WAREHOUSE TO ISSUE MATERIALS" ?

    It has nothing to do with column C "checklist2". It just some descriptions that play no part in this logic code.


    For condition B, this code should only refer on Col. C of "submitted" only. It should be filled with "painting/coating/airsheet" only depend on its workcounter respectively in order to result "yes".

  29. #29
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    Nahh...Its looking good for now. Just got all "yes" for column H. I guess i got what i need now and will try not to modify it again...But still i will test it with hundreds of data later.


    Anyway, thanks for helping Jindon.Really appreciate your passion and patience .

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    OK, just post back when you find the bug.

  31. #31
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    dear jindon..

    i have tested ur code , plug in them in my workbook. But seems it doesnt work well when used for large amount of data.

    I think the problem is not about your code. Everything just find i guess. Maybe something about my workbook is not right. Do you mind to take a look?

    Then i will attach my workbook, and indicate the data that have the problem.

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    I can not say anything without seeing your file.

  33. #33
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    For example is row#374 (Checklist2). It should be 'yes' because it fulfill both condition A and B at row#724 and row#1827 (Submitted).

    whats ur opinion?
    Attached Files Attached Files

  34. #34
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Comparing data between two worksheet , based on multiple conditions

    Your filter affects the result
    Please Login or Register  to view this content.

  35. #35
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Comparing data between two worksheet , based on multiple conditions

    thanks jindon..

    never expect that filter stuff can lead to error..

    Thanks again..

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1