+ Reply to Thread
Results 1 to 3 of 3

Compare 2 worksheets, delete dupes and group by back fill color using macro

  1. #1
    Registered User
    Join Date
    08-20-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Compare 2 worksheets, delete dupes and group by back fill color using macro

    I'm working on a project new to the VBA code to latest version of Excel. It's been a long time since I've written any code and trying to automate one of my daily tasks at my work. Have a spreadsheet with 9 columns of data with varying rows. I have a current worksheet I work from daily and need to to compare with new updated worksheet with new data. I have to remove the items from the current sheet which are not listed on the new sheet moved to a 3rd worksheet. Delete the duplicates which shows up on the new daily data - keeping my existing work and add the new data to my existing work then group the various sections highlighted in background fill color.

    Is this possible? I have recorded a macro to get the initial leg work on it but having problems getting it to work properly with a variable amount of rows changing on a daily basis

    Any thoughts or ideas would be greatly appreciated to automate this very time consuming task.
    -Larry-

    Here is what I do manually:
    I open my current worksheet I work on and update on daily basis, I align my data in column A to left justified.
    Open the daily report for today's work cut and paste at the bottom of my current worksheet.

    Manually scan through and see what data in column A is left justified and not duplicated, cut that data and paste into my ongoing list data on worksheet 3.
    After going through the whole list, remove the duplicates out of the entire row if column A is duplicated.
    Take my end result and background fill by level's of work, Yellow, light red, Red, no color fill, light purple, and green and group them all together.
    Last edited by jousterlj; 08-25-2011 at 01:50 PM. Reason: better description

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,227

    Re: Daunting task Is this even possible?

    Hi jousterlj and welcome to the forum.

    I've solved many "daunting tasks" by doing a little piece at a time. VBA is very robust and can accomplish what you describe above but there are different ways to attack the problem(s).

    One hint in my VBA which I use in almost all code is determining how many rows of data I'm dealing with. I use the following code to determine it.
    Please Login or Register  to view this content.
    I've also used the following line of code to open a file. You might use this for opening your daily file.
    Please Login or Register  to view this content.
    Followed by
    Please Login or Register  to view this content.
    My suggestion is to record little pieces of your process and understand what the recorded code is doing and then generalize it to make it work for any file or any number of rows.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    08-20-2011
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Compare 2 worksheets, delete dupes and group by back fill color using macro

    Thank you for those whom replied, I found a solution with help of a friend to simply use a match formula to make my changed data stand out so can easily identify and move myself. This will work until I can get through the VBA script for dummies and get myself updated with today's code.

+ 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