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

If you are interested about manual method instead of VBA/Macro check my previous post.

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

This Code has 5 steps which will describe here.

First Step

Declaration of  variables and assigning variables

Dim a As Long, b As Long, c As Long, d As Long 

Dim i As Integer, j As Integer, k As Integer, l As Integer

Dim dl As Worksheet, da As Worksheet, wo As Worksheet, wk As Worksheet 

Dim st As Variant

Dim rn As Range

Application.EnableEvents = False

Application.ScreenUpdating = False

Set dl = Sheets("Data List")

Set da = Sheets("Dashboard")

Set wo = Sheets("List Without Keywords")

Set wk = Sheets("List With Keywords")

There are 5 sheets in this workbook. I have assigned 4 of them to variables for easy use. Then turned off events and screen updating to speed up the macro,

Second Step

Clear available data from output sheets and find last rows of input sheet and Keyword list. (Keyword list is on the column A of the Dashboard Sheet.

a = dl.Cells(Rows.Count, "A").End(xlUp).Row

i = dl.Cells(1, Columns.Count).End(xlToLeft).Column

j = da.Cells(Rows.Count, "A").End(xlUp).Row

wo.Cells.Clear

wk.Cells.Clear

dl.Activate

dl.Range("A1", Cells(1, i)).Copy Destination:=wo.Range("A1")

dl.Range("A1", Cells(1, i)).Copy Destination:=wk.Range("A1")

b = 2

l = Range(da.Range("B2").Value & 1).Column

variable b is going to use as row number of first row which is blank in the sheet List With Keywords sheet. Initially it is 2 

Step Three

Loop through every cell in keyword list, filter data set using the each keyword and copy data with keyword to the sheet List With Keywords. Finally visible cells in the Data List sheet colored using yellow.

For k = 2 To j

st = da.Cells(k, "A").Value

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Criteria1:="=*" & st & "*", Operator:=xlAnd

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wk.Range("A" & b)

b = b + rn.Cells.Count / i

rn.Interior.Color = RGB(255, 255, 0)

End If

Next

During every iterate of the loop b recalculating to have last empty row number. Here we can't use rn.Rows.Count because rn is not a continuous range. Some rows are hidden according to the filter and therfore rn.Rows.Count give the count of first continuous rows which is not correct. To overcome this error this code calculate all cells in the range and divide it by number of columns. (Cells Count = Columns Count * Rows Count)

Step Four

Filter the Data List sheet to show all rows which are not having any color and copy those data to the sheet List Without Keywords. These are the subset of Data List sheet data which don't having any keyword.

dl.Range("A1", Cells(a, i)).AutoFilter Field:=l, Operator:=xlFilterNoFill

Set rn = Nothing

On Error Resume Next

Set rn = dl.Range("A2", Cells(a, i)).SpecialCells(xlCellTypeVisible)

On Error GoTo 0

If Not rn Is Nothing Then

rn.Copy Destination:=wo.Range("A2")

End If

dl.AutoFilterMode = False

dl.Cells.Interior.Pattern = xlNone

After all filter in the sheet Data List is removed using AutoFilterMode=False and all colors add to the cells set to none.

Step Five

If one row in Data List sheet included more than one keyword, macro copied it more than one to the List With Keywords sheet. Step Five is to remove those duplicates. If there is more than one row to consider .RemoveDuplicates method need to have all column numbers as a array. First four lines of this part is to build that array.

ReDim st(0 To i - 1)

For a = 0 To UBound(st)

st(a) = a + 1

Next

wk.Activate

wk.Range("A2", Cells(a, i)).RemoveDuplicates Columns:=(st), Header:=xlNo

Step Six

Finally code will display the Dashboard sheet and enable Events and screen updates.

da.Activate

MsgBox "Filter Complete", vbInformation

Application.ScreenUpdating = True

Application.EnableEvents = True

During this part code notify the user that process is over. If you turned off events and screen updating during any macro, make sure to turned on within the macro, Otherwise they are turned of until you close all opened excel files and open excel.

You can download the sample excel sheet with the macro using below link

Clean Data List.xlsm

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

Show Hide Sheet Tabs in Excel

As I'm an excel macro developer, I'm receiving 4-5 excel files a day from my potential customers. One day I was wondering that there was no way to navigate through the sheets. I have checked if the sheets are hidden, no they are visible but can't navigate. Then I did a search on the google and found the solution.......!. Excel allows us to Hide sheet tabs. Let's see how.

• Click on Office Logo on excel 2007 or File on later versions
• Select Options from that submenu.
• Now you can see a new, popup window appears.
• Select Advanced from the left buttons.
• Scroll down to the Display Options for the Workbook section.
• Tic the option Show Sheet Tabs. (If you are going to hide the sheet tabs, remove the tic)
• Click OK.

Here is a picture about the Display options for workbook section. It shows how to show hide scroll bars on the excel.

Please make sure to have some kind of option like hyperlink or button to navigate through sheets if they are still needed to view.

You can watch the video here