VBA for pulling out info on changes

[areskoug]

Hi,


I'm trying to understand how I can write a function or VBA-script that would help me with the following:




-A sheet will contain tabs (1 for each month) in the following format, YEAR-MONTH so the tab order may look like "2016-11, 2016-12, 2017-01, 2017-02"
-Every tab will contain lists of people as of row 13. A person will have a unique identifier in column A.
-Column D will have numbers in the format of XXXXXX-XXXX.
-Column G will contain a numeric value that may be different depending on tab.


In another tab named "Changes, I would like to run through tabs in the format mentioned above (could use a list in the sheet which contains names of the tabs) and check if the latest tab (in this example "2017-02") has seen a change in column D for a person where the 4 latest numbers have changed from 0000 to something else (the 6 first will always be real numbers). If there has been a change, list the uniquie identifier in column A of the separate tab for the person, the latest numbers from column D and sum the values in column G for all the tabs (months) in the past into column G of the row.


As an example for person A the history may look like this:
Tab "2016-12"
Column A: 10002
Column D: 1980-07-03-0000
Column G: 25000


Tab "2017-01"
Column A: 10002
Column D: 1980-07-03-0000
Column G: 30000


Tab "2017-02"
Column A: 10002
Column D: 1980-07-03-3548
Column G: 25000


The result in the tab Changes would return the following for person A:


Column A: 10002
Column D: 1980-07-03-3548
Column G: 80000


I have uploaded an example here https://drive.google.com/open?id=0BxKHC-GwHkMoRHFnaXhONHgySTA

I highlighted the person in yellow where the formula would pick up that there has been a change in the D column for that person and as such that person is listed in the "Changes" tab.


Let me know if it's still not clear.




Any tips on VBA or Formula to achieve this would be hugely appreciated!






Thanks,
Johan

 

[Russell Hauf]

Since no one has responded in a while...is there any way that you can get the data all into one sheet? Having one sheet per month, while easy to tell what month's data you are looking at, makes things much more difficult not just for the scenario you are describing above, but for many other things as well. I'd suggest trying to put all of the data in one sheet. Add a column that designates the Year/Month combination (I suggest in the format of YYYYMM or YYYY-MM, so it sorts correctly). Then sort it by the Year/Month, PersonID (Col A), and then by Col D. After this, you should be able to easily apply a formula (or code, if you so desire, but it should be do-able using a formula).

If you don't think this is a feasible solution that you could implement moving forward, then if it was me, I'd use code to combine the sheets (or at least the parts from row 13 down), then go from there.