+ Reply to Thread
Results 1 to 25 of 25

Compare Columns - Clear Master List Cell

  1. #1
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Compare Columns - Clear Master List Cell

    .
    Trying to understand why this macro is not providing the intended result :

    Please Login or Register  to view this content.
    Refer to the attached workbook .. Sheet 3. There is a comparison of what cells in Col A should be cleared and what is actually occurring.

    Thank you for the assist.
    Attached Files Attached Files

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    I'm not getting the issue here but following gives me same result as sheet3-column4.

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    Thank you for your response.

    The code you provided deletes all of the numbers that should remain visible ... and leaves the numbers that should be erased.

    I tried editing the code to obtain the required results but have not been successful.

    Any suggestions ?

    Thank you so much.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Compare Columns - Clear Master List Cell

    The problem with your original code was simply that the data sets were not in the same rows so the i variable referred to different data on each sheet.
    Rory

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    rorya

    Well .. there you have it ! BINGO ..... solved !!!!

    Once again, something small ... easily overlooked. Duh !


    Thank you to all for your assistance !!!!

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Please Login or Register  to view this content.
    Last edited by bakerman2; 02-17-2020 at 07:04 AM.

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    Yes ... I changed the number 4 to different numbers ... but still didn't reach the desired result.


  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Now you got me confused too.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Quote Originally Posted by Logit View Post
    Trying to understand why this macro is not providing the intended result
    For starters : [Sheet3!A2:A21] = [IF(Sheet1!G6:G25>"","",Sheet3!A2:A21)]

  10. #10
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2 :

    Please see the updated example project attached with additional explanation ...

    Thank you !



    rorya had indicated the original code was not functioning as desired because the data on both sheets were not aligned in the same rows.

    It doesn't concern me if the data in Sheet 3 ONLY begins in A6 or A2 (to allow for a header). The data in Sheet1 MUST begin on row A6.
    Attached Files Attached Files
    Last edited by Logit; 02-17-2020 at 04:32 PM.

  11. #11
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    Marc L

    Thank you for your response. My mind is in melt down at the moment. Been working on this challenge for several days now.

    Let me see if bakerman2's suggestions solve it first before I start to focus my mind on something else. I trust you understand.

    Thank you so much !


    ps: Didn't want to leave you hanging in the wind. I know how that feels and it isn't a good feeling.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    No problemo, it was just to show you another way without looping …

  13. #13
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    Here is another thought that may make it easier to secure a solution.

    This macro does precisely what is needed BUT, because it refers to the cell value as an integer ... it won't allow the use of characters. Example: Unit "101" can't be named Unit "A101" or even "101A".
    I've done my best to edit the macro in a way that would permit the use of characters along with the integers but obviously haven't been successful.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Sorry for delay but have a go with this one.

    Please Login or Register  to view this content.

  15. #15
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    Apologies for my confusion ... (I guess this is becoming convoluted ) ... which work book were you using for the code in your post #14 ?

    If possible could you post the wb here ? Thanks.

  16. #16
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    The last one from Post#13. Using Sheet1 & Sheet2, with results on Sheet1.
    Attached Files Attached Files
    Last edited by bakerman2; 02-18-2020 at 11:52 AM.

  17. #17
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    We appear to have a winner !!!!

    Thank you so much. I'll work with this today, implementing it into the project.

    Thank you again bakerman2.

  18. #18
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    I've managed to adjust the macro so it recognizes Sheets 1 & 3. These are the sheets used in the final project.

    What I can't seem to understand is how to get the final list of Available Units to display in Sheet 1 beginning at A6.
    Please explain how to edit the macro to accomplish that.

    Updated example wb attached.

    Thank you.
    Attached Files Attached Files

  19. #19
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Please Login or Register  to view this content.

  20. #20
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    bakerman2

    Thank you brother ! Works like a charm ...

    I still have great difficulty following the logic of macros like this. So many variables, it boggles my mind.


    Again, thank you so much ! And this thread is truly SOLVED now.
    Have a great day !!!

  21. #21
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Forgot to take into account that there might be some data above A6 so maybe better to use

    Please Login or Register  to view this content.
    Glad to help and many, many thanks for rep+.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  22. #22
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    When data is entered into Col B, Sheet 1 .... the macro fails.

    SORRY .....

    I don't see in the code where it is looking at Col B.

    ????

  23. #23
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    This seems to have cleared that up. Let me know if you disagree.

    Please Login or Register  to view this content.

  24. #24
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Compare Columns - Clear Master List Cell

    Variable number of rows to be cleared.

    Please Login or Register  to view this content.

  25. #25
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Compare Columns - Clear Master List Cell

    .
    Thank you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. macro to compare and clear cells in two columns
    By ncarrocino in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2016, 01:13 PM
  2. Replies: 5
    Last Post: 10-26-2013, 01:03 PM
  3. [SOLVED] VBA Code - Compare Adjacent Cells In two columns and clear contents when a match is found
    By Langer101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-03-2013, 06:22 AM
  4. [SOLVED] Compare Col A across 2 Sheets in different Workbooks, Copy Matches to MASTER list, H/L NON
    By ali.whitaker in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2012, 04:49 AM
  5. Compare 3 lists of data against a master list
    By BrianHawn in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-22-2011, 12:44 AM
  6. [SOLVED] Compare master list and update
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2005, 01:05 PM
  7. [SOLVED] Compare Master Account List with Partner Accounts
    By Martin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2005, 11:05 AM

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