Macro to push blank data field records in the end.

[e4excel]

Dear Forum,

Daily, I work on an Incremental Data and there are certain data fields which are missing as this common data is circulated to all departments some fields are missing to prevent data leak..

However, for my work I need to have all the columns / data fields filled with values so the person sending data sends 2 Files one with Masked Data and another which has the Missing Information which I have to collate using vlookup on a daily basis, this is very taxing and a very bad methodology to follow..

However, on the pretext of securing the confidentiality of the data, the management does not want to change.
So, I have to do this rigorous task daily...of collating the missing chunks..

What I normally do is ? I create a copy of the original sheet in the same workbook Ex: Main Sheet is "Log" so the copy would be "Log (2)" and then I put a Auto-Filter and then put a Custom Filter on the Columns which has missing data and then Cut this data into a NEw Sheet and Name it as "Masked Log as on Day - 1" and then go back to Sheet Log (2) and then delete those records and then OPEN/REMOVE the filter and then copy paste the missing records from the just created sheet "Masked Log as on Day - 1" and then paste them exactly below the last filled record in the "Log (2)" Sheet and then do a VLOOKUP based on a specific unique EMp Id Column from a another WorkBook which has a Sheet called as "Masked Log" and retrieve the missing details.

As this Masked details are send on random basis there is every possibility that certain details would remain missing which remain blank in the final report and so we push the records down.


So can someone help me with a Macro to achieve the same..

I have recorded a Macro but I dont know how to make it Dynamic..as everytime different no of records would be missing and I want this to run till all the details are filled and then after checking for all the missing details from the Masked Log Sheet in the other workbook we do one more round of pusing the data down further so that it becomes easy to track in the final report..

Please Login or Register  to view this content.

I have coloured this range to understand that these records came without the details if there is any other scrutiny to be carried out..later..

Regards
e4excel

[e4excel]

Dear Forum,

I have made a sample workbook for explaining the requirement, the Recorded Macro was done on the actual data and therefore the Columns would not match however this file is a dummy after removing the sensitive data columns...

There are 4 Sheets in this Workbook where the Main Sheet is "Log" which has some data fields missing coloured in yellow...

In the Sheet "Log (2)" the same records go down..

What I do ? I apply a Filter select the blank records copy them in a new sheet and then delete them from Log (2) and then paste them back from "Masked Data as on 1-Nov-13"

in the end below the last record in the Sheet "Log (2)" ..

The same data is present in another sheet "Masked Log" so by applying lookup formula I can get it back in the Sheet "Log (2)" and then again whatever is "#N/A" has to again go down further..

So all the records with some details out of these 3 columns i.e Application ID(Log Cases) in both the columns and the Contract No...need to get data back from Masked Log if possible or else just be pushed down to distinguish it from other filled data in the same Sheet "Log (2)"

The Yellow Fill colour records are the ones which are present in Masked Log Sheet and the one in green is not available...so can this macro loop till it fills back the data and then again pushes the records down after doing an INDEX LOOKUP from Masked log to the "Log (2)" Sheet..


Regards
e4excel

[e4excel]

Dear Forum,

Anyone has any suggestions to this daily task for Multiple sheets..

Regards
e4excel

[nilem]

Hi e4excel,
try it (if I understand correctly)

Please Login or Register  to view this content.