Link to home
Start Free TrialLog in
Avatar of Ted Penner
Ted PennerFlag for United States of America

asked on

Fast data scrub

I need a macro that will scrub excel data quickly while allowing you to eliminate a column and/or row from the scrubbing.

It should use the same number of random letters for the cells where letters exist and the same number of random integers where random integers or characters exist.

It should be exportable within a single module.

Assistance is greatly appreciated.
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Can you give us some example data with what needs to be scrubbed out and what needs to stay? It doesn't need to be actual data, it's just hard to tell what you're going for from your description. Also, see if you can add VBA or Visual Basic of Applications - that may attract more experts with macro experience.
Avatar of Ted Penner

ASKER

While I can't provide actual data that needs to be scrubbed, I can give the rules for it which I can't see any way to make clearer.

I need a macro that will scrub excel data quickly while allowing you to eliminate a column and/or row from the scrubbing.

It should use the same number of random letters for the cells where letters exist and the same number of random integers where random integers or characters exist.

It should be exportable within a single module.

Assistance is greatly appreciated.
Avatar of slubek
We don't need your actual data. We just want an example - some random generated data with desired output should be enough.
 
I'm not english native speaker, so I'm not sure what you mean by 'scrub'.
Thank you!
Try just a simple statement of what you'd like to accomplish, like "delete any rows where "Russell" appears in column A", or "look for "Russell" in any cell and replace it with a space character". I'm just getting tripped up on the phrase "same number of random letters".
I really paid close attention to how I worded that, and it is in my opinion, a perfect explanation of what I am trying to achieve with the macro.
Agreed - define what you mean by "scrub". To most that means "remove", but it seems like in your case you mean "replace" or "obfuscate".

If you mean replace, then I assume that you want to replace one value with an obfuscated value. For example, if the utility finds "Scott", it would replace that with something like "Autgg".

If that's what you mean, then just state it. Don't be obtuse, and when asked for examples please provide them. We're trying to help you, but we can't if you refuse to provide us basic information.
Right, replace would be correct, so that the data cannot be reassembled into the original data at all.  Thank you sir.
This is clearly beyond my original scope but I thank you for bringing up the three options (remove data, replace data, or obfuscate data). Those would all be great as choices if it were possible to include that in a single module.
Maybe you should provide an example. E.g. do you want to have   abcde123f4g   replaced letter by letter and digit by digit, getting say   nmxuq987x6k?
Yes but the digits should be random and not able to be pieced back together.
So to get it right:
* go thru all columns of the current sheet, but allow some to get exempt
* allow rows to get exempt
* replace each digit with a random digit
* replace each letter with a random letter
* do not use workbook or sheet names, to allow for generic code sitting in an exportable modul
Where should the exceptions get noted? On each sheet (needs fixed location or named ranges then), in a different sheet, in code, ...?
SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I received the attached error.  In both instances, when I clicked debug, the sheet closed instead of opening the debugger.

User generated imageUser generated image
What do you mean with "select"?
um??
The macro is not designed for using your active selection.