A macro code for filtering data based on some conditions

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.

Hi jessebranum777,
Thanks a lot for your help, but the problem is, i do not see any effect on the first and the second blank cell(is on the file).So the last read for the first account and the first read for the second account.
And my columns change everytime i receive reports

No man,
Everything in your code is fine, besides the two blank cell. when i run the macro, i see that code doesn't effect this two,as you can see on this two I am missing information.
To clarify I highlighted yellow the cell that i expect information from the code.
I attached the file*Book1 Updated.xls

jessebranum777 ,
I wanted to say thanks you for your time spending resolving my problem.
this code work fine in the example that I sent to you but not in my file.
I think the problem with the code is, it doesn't do anything or stayed block in the accounts with no data.
I am attaching my file, so the first account is all blank. Using the logic for this the code has to say missing.
Do you have any idea?
Book3.xlsx

Originally Posted by jessebranum777

Thanks for the sheet you had a variable I wasn't thinking about. Try this out.

Please Login or Register  to view this content.

Hey man thanks a lot for your huge help. I think i have again a problem. When I was trying to upload the file, i couldn't upload all of the file because was too big so i divided it in different parts. I sent to you one of them. So now when I run the macro in the big file doesn't go at the end. Maybe because you specify the end cell?
If you give me an e-mail address i can send you there.

Originally Posted by jessebranum777

Since you have excel 2013 we can change the macro to go down further in order to find the last row thats no big deal.
change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

Hey man,
I did that and it works fine.
What logic did you use for the multiple dates like one in cell 103, or 116 or 118?

I also have cases like one highlighted on the file where the date in the cell 39 is the same with one in the cell 41. so i am not missing anything here

jessebranum777, I wanted to say thank you for your time spending with my problems, i really appreciated it.
I will combine this code with one that I am working, but i need one last thing. I want to understand the code.
When you have time can you put some comments so i can understand more. I just started to learn macro and I can't say that my knowledge are as good as to understand the complicated situations like this.

Hey man we have a problem.
If you open the file book3 and run the last code, you'll see the comment missing on the row nr 93. On this kind of situation we are not missing something because is the day after in following cell(92-94)
I am trying to understand the code but in some part is very hard for me, i have the problem with location of the cell.I asked here in forum what is the best way to find the address and a member told ActiveCell.Address . so what i did was i placed this line (Range("f2").Value = ActiveCell) in the begging of the code but the code put the first address and doesn't change it. This is just for me to understand what you did.

jessebranum777,

I have some questions for some issues that I don't understand. Also I made some explanation in what I understood from the code.Can you please check my logic?

1.'SET B OR A VARIABLE TO A NUMBER OR THE ACTIVE CELL ROW NUMBER
Let B = ActiveCell.Row
Why do we have to set the value to the variable B? Started from C2 that means B is going to take the value of C2 since that is the active cell.

2. 'SET VARIABLE TO THE ACTIVE CELLS OFFSET (ROW,COL) SO TO THE RIGHT 2 COLUMNS AND .VALUE IS SETTING THE VARIABLE TO THAT CELLS VALUE
Let C = ActiveCell.Offset(0, 2).Value
Starting from C2 and using relative references ActiveCell.Offset(0, -2) that means that C is going to take the value of the cell B2 which is blank.
When and where do we use this value (C)?

3. 'THIS IS AN IF STATEMENT BUT IN THE MIDDLE IS AN AND STATEMENT SO THAT TWO CONDITIONS MUST BE MET
If ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(1, -2).Value And ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(-1, -2).Value Then
Based on this what I understood, is: started from c2, if the value on ActiveCell.Offset(0, -2), in this case is A2(the account number) is = with the one below A3 AND the value on A2(the account number)=with A1(in this case the headline , but I think, this check if the value on the A2 [activeCell.(0,-2)] is =with the one above it. This is the reason why the code goes at the end because the three value that you are comparing aren’t the same):
(Part of the code at the end, macro goes here when three values aren’t the same)
Else
ActiveCell.Offset(0, 1).Value = "MISSING"
ActiveCell.Offset(1, 0).Select

After this, you are using another condition saying:
If ActiveCell.Offset(-1, 1).Value <> "" Then
In what I understand is, started from the cell C3 and using ActiveCell.Offset(-1, 1) we are checking for the value in the cell D2, if this cell is not blank( Not sure but maybe you are using the value in D2 as reference where you realize that there isn’t any value for c3 to based on ), if this is true you are saying to put the value “missing” on ActiveCell.Offset(0, 1).Value, starting from C31 this cell is D3
After this, select the cell below C3, ActiveCell.Offset(1, 0).Select which is
C4 and this goes on until the macro find a value in cell c11

4. in the cell C20 and C21 is the same situation as in the cell c2

5. in cell c31 the condition: If ActiveCell.Offset(-1, 1).Value <> "" Then is false, so macro goes at else .
Start to select the cell D30 ActiveCell.Offset(-1, 1).Select here you put the last value of the cell c 31( I am saying the last value because some of them have multiple reads) using this ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],10)+1"
I don’t understand the condition below where you saying:
If IsError(ActiveCell.Value) = True Then
ActiveCell.FormulaR1C1 = "=MID(RC[-1],37,10)+1"
The others until Let X = ActiveCell.Value are fine. No clue why we use this Let X = ActiveCell.Value.
After this you delete everything and using ActiveCell.Offset(1, -1).Select go again to the cell C31, after you activated the cell B31 and you use a condition say to proceed until find a value. When you find a value, you use this :
If ActiveCell.Offset(0, -1).Value <> "" & c & "" Then
Range("D" & B & "").Select
I don’t understand the meaning of this above.
In the case of the cell B32 the condition is false so the code goes to else.
I don’t’ understand the purpose of : Let Y = ActiveCell.Value

After this the code select the cell B30 using the reference ActiveCell.Offset(1, 2).Select
At the end we have the famous condition, I understand the purpose but not the grammar
If "" & Format(X, "MM/DD/YYYY") & "" > "" & Format(Y, "MM/DD/YYYY") & "" Then
ActiveCell.Value = "NOTHING MISSING"
End If
If "" & Format(X, "MM/DD/YYYY") & "" = "" & Format(Y, "MM/DD/YYYY") & "" Then
ActiveCell.Value = "NOTHING MISSING"
End If
If "" & Format(X, "MM/DD/YYYY") & "" < "" & Format(Y, "MM/DD/YYYY") & "" Then
ActiveCell.Value = "MISSING"
End If

When I run the code in the book3 for the case in row number 93 which is “nothing missing” the code goes from Loop
If ActiveCell.Offset(0, -1).Value <> "" & c & "" to else Let Y = ActiveCell.Value
Selection.End(xlUp).Select
ActiveCell.Offset(1, 2).Select
And give at the end the proper result.

When I run the code to my original file(after doing the updates that I describe above)
In the case of row 93, code goes from
If ActiveCell.Offset(0, -1).Value <> "" & c & "" to then Range("D" & B & "").Select
ActiveCell.Value = "MISSING"
And give me the result Missing which is wrong (for the range, I am using X instead of D so Range("X" & B & "").Select

Do you have an idea?

Attached you'll see my headlines.
I highlighted red the columns that we are working.

Originally Posted by jessebranum777

To be completely honest, it would be easier to just modify the macro to move the appropriate columns before it even starts and move them back after the macro has run if you need them to be in the specific columns rather than trying to modify the macro that has no absolutes and is based on data being in certain cells and rows and columns being in certain spots.

The grammar part you are asking about it exactly what you said its adding 1 to the date it pulls from that to date cell and comparing it to the from date. Some scenarios made the date more and some made it exactly the same. I can't remember which at this time. As for the rest again its better to adjust the macro to adjust the columns of the worksheet then run the macro then put the columns back where you want them rather than changing the entire macro and run the risk of messing something up.

IE

Please Login or Register  to view this content.

Thanks jessebranum777 for your information. since I don't have too much knowledge no macro I didn't know that fact that you're mentioning above.
The problem is, I can't change the columns of my report, because we use a standard and also our partner use our standard.
what is the purpose of the code above? where to place it?

This looks fine; but i saw a problem, this problem appear to be also before you did the last update.If you run the code to the file book3(the code before the last update) and check the row nr 117 you'll see the message" missing" in this case is not missing because the last value on the column C is 9/15/2013 and the first value on the column B row 118 is the next day,9/16/2013.
DO you have any idea?

ahhaah i found that doing a bunch of tests. thanks for your help man. I am testing that in different report that i have and it looks fine. I'll do some other test and i'll mark as solved.
I have one last question for this If ActiveCell.Offset(0, -1).Value <> "" & c & "" Then
why you use two "" "" so to blank instead of one.

ahahha I am scared to change it because i dont want to ruin it

Hey man,
I spend three days testing the macro and i saw something. At the end, when macro copy and paste columns A, B, C, D to respective columns N V W and X it always freezes.
what do you think that we can do?
What can do to not copy and paste and to work directly to columns N, V W and X.
I am so mad, everything was working fine since I found this.