+ Reply to Thread
Results 1 to 25 of 25

VBA to Compare 2 Worksheets by both a unique identifier row and column header

  1. #1
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Hi, I am am in need of a macro to compare 2 tables - both in the same workbook, on 2 different sheets.

    Table1 is a refreshable table with data updated daily
    Table2 is a table with only specific rows from table1 that were added to a new sheet previously

    table 1 starts at row B21 (headers)

    So, when I run the macro, I'd like to be able to highlight anything that has changed in table1 compared to what I have in table2.
    3 different forms of highlighting
    1. Red = If the "FileNumber" is no longer on table1 but is on table 2 (Highlight entire table row red)
    2. Green = If the data in table1 is filled in, but the data in table2 is not (data = the specific cell, found by the "FileNumber" row, and the column header name)
    3. Yellow = If the data in table1 is filled in, and the data in table2 is filled in, but there is a difference

    The comparison needs to be by "FileNumber" (This is a unique value) and the Column Headers name -- the columns wont be in the same order and table2 may be less columns than table1 (deleted by the user)
    but the names will be the same.

    I have attached a workbook with notes, explaining in more detail as I think that will be clearer

    This example is MUCH smaller than the actual workbook, which is around 15,000 rows and goes out to column CA -- but, hopefully it shows what I would like the macro to do

    Appreciate any help!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Hi,
    Try out this raw solution. You mentioned that original data contains ~15k rows, so loop and compare cells might be quite slow. All data processing is in arrays.
    My logic for this compare is:
    1) Get array with all headers of Table2 (this one probably will contain less data, but all columns in this table must be found in Table1).
    This array includes number of corresponding column from Table1 (as soon as columns can be mixed and order is not the same as in Table1).
    Basically "Name" (header of Table2), 10 (that means that in Table1 column "Name" is number 10)
    2) Find and write to variables numbers of 'FileNumber' columns (FileNr1 and FileNr2)
    3) Transfer both tables into arrays
    4) Loop through array (with Table2 data) and compare data with another array (Table1 data) + write result of this compare to separate array ( Result() ).
    5) After all data is compared (inside arrays this should be fast enough) loop through Result array and fill cells with colors.


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

  3. #3
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Thanks for your help!
    I tried running this in the actual workbook and received the error: "Run-time error 13 - type mismatch" for the below line

    T2Headers(i, 2) = Application.Match(T2Headers(i, 1), Worksheets("Dashboard").Range("21:21"), 0) - 1


    any ideas?

  4. #4
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Row 21, of table1 is hidden if that is what may be causing it? (as well as the majority of the columns in the table)

  5. #5
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Okay, I actually got it working -- but, noticed that while it highlights the cells that change -- it does not input the new date -- what do I need to change to do that?

  6. #6
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Hi,
    Quote Originally Posted by BG1983 View Post
    it does not input the new date
    What do you mean by "new date"? Do you want to fill green/yellow cells with data from another table or...?

  7. #7
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Yes - sorry for the confusion - fill it in with the date from "table1" (This is an updated table, so woudl like that data transferred over, but be highlighted in the colors i identified to show that there was a change)

    also, i wanted to have the data that was in table2 but no longer in table1 show as the entire row being red -- not just the filenumber cell.

    I edited the code you provided below - which works, but id rather it be dynamic to only highlight the table -- regarldess of how many columns it is (ie count the number of columns and highlight to the one that is the last -- i see that your code already is declaring the last column number as a variable.)

    If Result(i, j) = 1 Then Sheets("Workload Tracking").Range(("B" & i + 4) & ":" & ("BN" & i + 4)).Interior.ColorIndex = 3 'red

  8. #8
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    I suppose you have already made some changes to fit code to your real data, so I'll highlight changes in red.
    main change is array Result now is 3-dimentional, 3rd dimention to keep new data from Table1
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Awesome - that's good stuff - thanks appreciate your help here!

  10. #10
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Okay - one last tweak actually - How can I get it to ignore the first column of table2? (Where the names are) -- If I have the column named "Assigned To" it will throw an error because it cannot find that column in table1.

  11. #11
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Well, simpliest way to do this (within existing code) - just rename "Assigned To" to "Name" before compare and put "Assigned To" back after.
    This actually will include this column in compare process (I suppose you would like to see values of this column Green/Yellow in case of changes).
    So here are parts of the code (red code should be included in last version):

    Please Login or Register  to view this content.
    And this code should be at the end (just before End Sub)

    Please Login or Register  to view this content.
    Last edited by kasan; 01-20-2017 at 10:59 AM.

  12. #12
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Actually, I just need the macro to just ignore that column... neither "Assigned To" or "Name" exist in table1... both "Assigned To" and "Name" are both for the same column, I just happened to name them differently in my example workbook and when I was testing it.

    That column shouldnt ever change and does not need to be compared.. it just needs to be ignored when the macro runs

  13. #13
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Actually, I just need the macro to just ignore that column... neither "Assigned To" or "Name" exist in table1... both "Assigned To" and "Name" are both for the same column, I just happened to name them differently in my example workbook and when I was testing it.

    That column shouldnt ever change and does not need to be compared.. it just needs to be ignored when the macro runs

  14. #14
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Ok, I got your idea now. I can't do this right now, I'll check this when I get to my PC.
    Thanks for feedback and rep btw.

  15. #15
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Works for me -- yep, no prob

  16. #16
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Hi kasan, was curious if you were able to take a look at that last piece? Appreciate your time!

  17. #17
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Hi,
    One small thing to clarify: can we assume that this column Name/Assigned To is always as first column of Table2?

  18. #18
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Yes - Actually, its going to be 4 columns in table2 -- (Column B:E)

    Also, I was curious if it was possible to only compare the visible columns in table2... I was thinking that if we could, this would speed up the macro significantly since there are so many columns and removing them is not an option.

    Thanks!

  19. #19
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Here is your code that I am currently using, confirmed working in my actual workbook.

    Please Login or Register  to view this content.

  20. #20
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Quote Originally Posted by BG1983 View Post
    Yes - Actually, its going to be 4 columns in table2 -- (Column B:E)
    Column B is always "Assigned To" = and we should ignore this column. So working data is in columns C-F (4 columns).

    About comparing only visible columns - could be done, but then we will need to remember position of value (row,column) - this might complicate proccess, lets try something simple first.
    If your need is to compare only 4 columns from Table2 - why not just place whose columns in columns from C to F of Table2?
    As a result Table2 should looks like: column B = "Assigned To" (ignored in process), columns C to F contains data you need to compare, other columns can stay - all of them will be ignored in process.
    BUT make sure that one of columns in C to F is "FileName" column.
    It's total mess with array's indexes now, but this code works for me on test data.

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Sorry, I wasnt clear --- columns B:E are data that is added seperately and should be ignored. The actual data is columns F:AR --- FileNo is in column F (and always will be)

  22. #22
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Yeah, lets correct this again!
    Could you please upload normal sample file with number of columns exactly the same as in your real file?
    And please mark columns that should be ignored, should be compared, columns that will always stays in the same place.

  23. #23
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    Okay, i will send tonight. But, was thinking that i can make the column order the same for each table IF we can edit the macro to only process the data in the visible columns... the user needs to be able to either delete or hide the columns they dont need to compare, in order to speed up the macro. (They dont have the 16gb ram that i do.) I will add coloring to the example workbook to make it clear.

  24. #24
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    attached is the workbook with the column info -- think i got everything in there.. put like 20-30 rows of filler data in there.. .(they two tables are identical right now)
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    08-08-2015
    Location
    North Carolina USA
    MS-Off Ver
    MS Office 2016
    Posts
    368

    Re: VBA to Compare 2 Worksheets by both a unique identifier row and column header

    No luck I assume..?

+ 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. SQL code sum by unique identifier in column only show max result
    By Jack7774 in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-26-2016, 04:11 PM
  2. Replies: 1
    Last Post: 07-08-2015, 10:07 PM
  3. Replies: 2
    Last Post: 04-16-2014, 05:13 PM
  4. Merging data using unique column identifier?
    By jjinca in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 05-14-2013, 07:55 AM
  5. [SOLVED] Counting a column of numbers based on a unique identifier
    By aaanenson in forum Excel General
    Replies: 2
    Last Post: 08-23-2012, 09:01 PM
  6. Combine column- two lists, with unique identifier
    By bchalker in forum Excel General
    Replies: 1
    Last Post: 07-09-2008, 11:14 PM
  7. Combine data from 2 worksheets using unique identifier
    By CathyW in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2006, 10:10 PM

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