Book1.xls Hi everybody,
I need your help especially, from you experts. I am thinking to run a macro but the situations is a little bit complicated.
I will start opening the attached file Book1
On the file you see three different accounts (I used three different colors, for avoiding the confusion) and information for them in two columns (date information, accounts have monthly information). The accounts are below each others, with no blank cell for separation, but as you see there are some blank cells in two other columns. The meaning of this blank cells is that I am missing information.
My idea is to create a new column saying missing information for each blank cell. So far is very easy and I can do this just using filter for the blank cells.
BUT, the situation starts to get complicated in the highlighted (red) case. Account no 528635478, the first read start in 12/14/2012 and end in 01/16/2013. The second read is blank, but we don’t miss anything because if you see the third read, start the day next(01/17/2013) after the first end read(01/16/2013). So in this situation when we have blank cell but third read start directly when the first finish, we are not missing any thing, for this one I want to say that we are not missing on the inform column.
It’s very important to do not use the information above or below the blank cell if the accounts are not the same. In the list, accounts are below each other’s but this doesn’t’ mean that information have a connection or are in continuity, this is only true when account is the same. Only when the account is the same, reads are in continuity with each-others the opposite is not true.
For example, I can’t use last of the first account for checking the first read of the second account.
My assumption is to put a filter for each account and after that check for the missing or not missing information. But I want to do this without any interruption, since I have to check 1000 accounts.
I would really appreciate if you find me a macro for resolving this problem. I also wanted to emphasize this is only one part of what I am working on.
Bookmarks