Have spent a lot of time on the Web trying various formula’s and VB to compare two worksheets in the same workbook and produce results in the way I would ideally like but given my average level of skill and knowledge I haven’t had any success. I’ve posted on a couple of forums with a couple of replies which I greatly appreciate but haven’t the skill to make those work either. I know exactly what I want; maybe it can’t be achieved in Excel 2016, hopefully someone can assist.
I can run and export into Excel a membership list from a third party Oracle database as frequently as I like. These reports comprise 15 columns with one row per member and up to 25,000 rows (members).
1st data cell is B5 – end data cell is P25000
As a rule, there is always a difference between each list as to the number of members on each list, for various reasons. However, it’s simply not practical to try and manually find and isolate the differences; that is, who has been dropped off (or been added) to the membership list between each run, so I am hoping someone can tell me if it is feasible for VBA to do what I want and give me the code?
I have attached a sample workbook containing 50 records and representing the results I’m after.
The workbook has four (4) sheets: 1_Previous, 2_Current, 3_Dropped, 4_New.
Presumably by clicking a Command button the code will do the following:
a. Compare 1_Previous with 2_Current;
b. For every record that appears in 1_Previous but does not appear in 2_Current;
c. Place a COPY of those records in 3_Dropped in alphabetical order by member Surname;
d. THEN, for every record that doesn’t appear in 1_Previous but does appear in 2_Current;
e. Place a COPY of those records in 4_New in alphabetical order by member Surname.
Hope this can be achieved and thank you in advance for anyone who knows how.