Ted Penner
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.
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.
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.
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.
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.
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'.
I'm not english native speaker, so I'm not sure what you mean by 'scrub'.
ASKER
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".
ASKER
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.
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.
ASKER
Right, replace would be correct, so that the data cannot be reassembled into the original data at all. Thank you sir.
ASKER
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?
ASKER
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, ...?
* 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What do you mean with "select"?
ASKER
um??
The macro is not designed for using your active selection.