subtract values between two sheets and create summary sheet

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    hi

    I have data in two sheets I would match data between two sheets the standard should COL B,C,D together because COL B,C are similar the different in COL D so it should bring all of data in sheet SUMMARY and show the values in COL F,G if values in first sheet bigger than second sheet then the value show in COL F and tick mark wrong in COL E and if values in first sheet smaller than second sheet then the value show in COL G and tick mark wrong in COL E and if values in first sheet are matched with second sheet then tick mark right in COL E

    note: if there are new data in first sheet but not existed in second sheet it also show in sheet summary and the values show in COL F and if there are new data in second sheet but not existed in first sheet it also show in sheet summary and the values show in COL G

    thanks

    If I've understood you correctly, the attached version will recreate the SUMMARY sheet each time it is run, based on the data in the 2 OIL SALES sheets.


    I have left your original Summary sheet in the workbook (called SUMMARY-original)

    I have also left a sheet called SUMMARY-with formulas - this sheet can be deleted, it just shows the formulas used to derive the result, the macro builds these intermediate formulas when creating the final SUMMARY sheet.


    Lastly, I have left the SUMMARY sheet in the workbook but this has been created by the macro called CreateSummary.


    This macro combines the sales data, removes duplicates (based on BRAND/TYPE/MANUFACTURE columns), creates the intermediate lookup formulas and then creates the final formulas for the CASE/SURPLUS/DEFICIT columns.


    CC.xlsm

    this is exactly what I want thanks so much

    just I 'm asking if I decide highlighting the new data are existed in first sheet but not existed in second sheet by red color and the new data are existed in second sheet but not existed in first sheet by blue color , may you guide me how add this in your code ?

    You are welcome.


    I have updated the attached sheet which will color the rows on the SUMMARY sheet as red/blue as you requested (you can change these colors in the macro as required).


    As before, I have retained the sheet called SUMMARY-with formulas - this sheet can be deleted, it just shows the formulas used to derive the result and now includes the extra formulas used to help derive the rows to be colored.


    CC.xlsm

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login