VBA code to compare and update column

  • Hi, Can someone help me with a VBA code for the below I tried but couldn't get at anything that works. Thank you inadvance.

    below is sample data list which is present in sheet1.



    Name1 - Column AName2 - Column B
    A
    A1
    A2
    A3
    A4
    B
    B3
    B2
    B4
    C
    C5
    C6
    Grand Total



    below is sample data list which is present in sheet2.



    Name1 - Column AName2 - Column B
    A
    A1
    A2
    A3
    A4
    B
    B3
    B2
    B1
    B4
    C
    C5
    C6
    D
    D7
    Grand Total



    Code has to compare column A data from both sheets and if anything new is found then that needs to be added above the grand total in column A (In this case it is name D). (any new data will be present in sheet2 hence that needs to be added to sheet1)

    then it has to compare combnation of colum A and Column B data in the both sheets and if any combination is not present then that needs to added below the last occurance of respective name1 row.(In this case we have B-1 which is not present in the output and has been added to 11th row as 10th row is last occurnce of B. similarly D-7 has been added to 16th row.).



    below is the expected output that wil be present in sheet1.



    Name1 - Column AName2 - Column B
    A
    A1
    A2
    A3
    A4
    B
    B3
    B2
    B4
    B1
    C
    C5
    C6
    D
    D7
    Grand Total



  • You need to attach an example workbook

    Hi royUK,


    I have attached the workbook. Just to clarify again. Consider A and B column data in sheet1 and pivot are senior manager and manager. Code has to compare both and if pivot has anything new then it has to add it to sheet1.


    There will be 2 scenarios.


    If new senior manager is found then that name should be updated in last before row (in which data contains) of column A.


    If new manager is found then that should be updated after the last occurence of respective senior manager in Column A of sheet1.

  • Your PivotTable is based on the hidden sheet called Source Data. You should be comparing that sheet with sheet1. It still won't be easy because of the layout of sheet1

    Hi royUK,


    May


    I know comparing source data sheet with sheet1 is difficult that is the reason I created pivot. Can you help considering data will always be there in column A and B of pivot. I am confident that I can manage it.


    Can you help me to get the desired output and I am okay if you alter the layout of sheet1. Thank you in advance.