Have you ever wanted to way to create distinct lists for your data to help you clean and transform values? Normally, you are relegated to copying a column of data, pasting the data in an unused range then running remove duplicates. This process can add up in time spent to accomplish this task for one or more columns of data.
In this post, I will go over my Microsoft Excel macro tool that can accomplish the task in seconds for your entire worksheet of data.
Access Macro from MS Excel
If you are currently in MS Excel, you can press Alt+F11 to open up VBA or navigate to the Developer Menu and select “Visual Basic” button on the far left side of the ribbon. Once you have VBA open you can import the .bas file. The file is located on GitHub here. You can just use the DataConditioningToolkit.bas, which contains a whole basic data toolkit of macros.
When you have the associated macro imported, and configured the way you want, you can run it against your data. To the run the macro, click the “Macros” button in the “Developer” ribbon. In the window, select “CreateDistinct_lists”.
This tutorial will just show you the operations and results with explanations.
Data Toolkit
If you followed the customize the ribbon post successfully, then you can just press the Create Distinct Lists button instead, which will run the macro.
Running the Macro
Our data can be on any worksheet and the macro will run on whichever is currently active. To activate a specific worksheet just select the appropriate worksheet and select a cell. From here the process will assume this is what you want to process. For example purposes, our data looking like the following:
Process Complete
Once you click or run the appropriate macro, the macro will process the entire worksheet putting each column on a new worksheet with an adjacent column labeled after the original with an extension “_clean”. Every column is processed.
The results of our particular example data look like the following:
Inspection and Validation
At this time you can view and inspect the output. Dates can sometimes change formats automatically in MS Excel. The results are sorted alphabetically.
What Should You Do Next?
What you do next is up to you and your tasks. There are a number of tasks you could do with this. The structure is prepared to help you create lookup tables for each column to help you clean and transform the data. This is likely the main utility, but not the only. Maybe you just want to calculate the distribution of values. Everyone has their processes and ways of accomplishing the same task.
In the next post, I’ll present the next step in the process if you are creating lookup tables to clean and transform your data. The process of merging the original data and the cleaned values is just as quick and simple with my macro.
Consulting Assistance
If you like what you read here and think there is an opportunity for me to help your business with creating a custom solution, please reach out on my Freelance & Consulting page.
