Clean / Filter, Set of Data Using a Keyword(s) - Part 01

When we have a large data set we have to filter / clean it before use. There are many blank rows / columns, unwanted spaces, etc in the data set. We can remove most of them using the tool developed by KTK Excel Tools. You can find out about it from below link

LK Tools Tab for Ribbon

Anyway this article is not to describe how to use it.

If we have to remove some of the data according to a list of keywords, How can we do that? Here is the simplest manual method I can imagine.

1. Filter data set using one keyword.
2. Copy the visible data to a new sheet.
3. Change the visible cell color to Yellow
4. Filter data set using next keyword
5. Copy the visible data to the same sheet which has data from step 2. (Paste this data to the bottom of the sheet)
6. Change the visible cell color to Yellow
7. Repeat the steps 4, 5 and 6 for all keywords
8. Remove duplicates in the new sheet which has copied data. (This is a subset of the beginning data set which including keywords)

If you need to have data subset which are not included keywords, Follow Below steps.

9. Go to the sheet which has all data
10. There are some rows having yellow colored by the previous steps.
11. Filter the data set using color filter to show cells without any color.
12. Copy visible data to new sheet.
13. This is the data subset which is not included keywords.

As a process, this is simple to understand and follow. However, if we have more keywords to look, the time taken to complete this process will be huge. We can automate this process using a macro. Check My next post for the macro.

Clean / Filter, Set of Data Using a Keyword(s) - Part