Macro to filter text based on multiple criteria

cacos · Jan 11, 2013

Hi everyone!

I'm currently using array formulas to sort cells with text comments on them, based on criterias that appear on the 6 columns preceding the one with the text.

It works, but it's really REALLY slow. I'm thinking if there's a VBA solution to make it run faster.

Clarification: The sheet with the raw data needs to remain hidden.

An example would be (example data, I'd need to make a new list with the "comments" on it, based on the 6 criterias defined from another sheet, if possible sorted alphabetically):

[pre]

Code:

Company	Region	Country	City	Vehicles	Parts	Comment
Pepsi	NA	US	NY	Truck	Part2	It was good
Pepsi	Europe	Belgium	Bruxes	Boat	Part1	Wrong
Pepsi	Europe	Norway	Oslo	Car	Part3	Issue with order
Coca Cola	APAC	China	Beijing	Truck	Part3	Missing parts
Coca Cola	Europe	Norway	Oslo	Car	Part2	OK
Mountain De	APAC	China	Beijing	Plane	Part3	OK
Pepsi	APAC	Taiwan	Taipei	Plane	Part3	Good
Pepsi	APAC	Taiwan	Taipei	Truck	Part1	Bla bla bla
Coca Cola	Europe	Italy	Milan	Car	Part2	Bla bla bla
Mountain De	Europe	Norway	Hamar	Plane	Part2	Bla bla bla
Pepsi	NA	US	Los Angeles	Boat	Part2	Bla bla bla
Pepsi	Europe	Italy	Rome	Boat	Part3	Bla bla bla
Pepsi	APAC	China	Guangdong	Boat	Part2	Bla bla bla
Coca Cola	APAC	Taiwan	Taipei	Truck	Part3	Bla bla bla
Pepsi	NA	US	NY	Boat	Part2	Bla bla bla
Coca Cola	NA	US	Boston	Boat	Part1	Bla bla bla
Coca Cola	NA	US	Los Angeles	Truck	Part1	Bla bla bla
Pepsi	APAC	China	Guangdong	Car	Part1	Bla bla bla
Pepsi	NA	US	Boston	Car	Part1	Bla bla bla
Mountain De	APAC	Taiwan	Taipei	Boat	Part1	Bla bla bla
Mountain De	APAC	Taiwan	Taipei	Truck	Part3	Bla bla bla
Pepsi	Europe	Spain	Madrid	Truck	Part2	Bla bla bla
Coca Cola	Europe	Norway	Hamar	Plane	Part3	Bla bla bla
Pepsi	Europe	Spain	Valencia	Car	Part2	Bla bla bla

[/pre]

Thanks!

cacos · Jan 11, 2013

Forgot to mention that it isn't actually filtering as it is creating a new list with the filtered data. Also, it's over 10,000 rows of data.

SirJB7 · Jan 11, 2013

Hi, cacos!

Would you please elaborate a bit more or upload a sample file indication which actions do you perform and what do you want to get displayed/sorted/...ed?

Regards!

cacos · Jan 11, 2013

Basically I'd need to create a new list, showing the text comments (those that appear on the row "Comment"), given that those comments meet 6 criterias. These criterias can be: Coca Cola, Europe, Italy, Rome, Boat, Part3.

So a new list would be created containing only those comments that match the 6 criterias that come from another sheet.

SirJB7 · Jan 11, 2013

Hi, cacos!

No need to use a macro to do that, it can be done just by formulas. Give a look at this file:

https://dl.dropbox.com/u/60558749/Macro%20to%20filter%20text%20based%20on%20multiple%20criteria%20%28for%20cacos%20at%20chandoo.org%29.xlsx

G30 has the count of items regarding A30:F30 criteria, and list of G30 values start at G31 and down. Here're the formulas:

G30 : =SUMAPRODUCTO(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30)) -----> in english: =SUMPRODUCT(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30))

G31:GXX : =SI(K.ESIMO.MAYOR(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30)*(FILA($G$1:$G$28));FILA()-FILA($G$30))=0;"";INDICE($G$1:$G$28;K.ESIMO.MAYOR(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30)*(FILA($G$1:$G$28));FILA()-FILA($G$30)))) -----> in english: =IF(LARGE(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30)*(ROW($G$1:$G$28)),ROW()-ROW($G$30))=0,"",INDEX($G$1:$G$28,LARGE(($A$1:$A$28=$A$30)*($B$1:$B$28=$B$30)*($C$1:$C$28=$C$30)*($D$1:$D$28=$D$30)*($E$1:$E$28=$E$30)*(ROW($G$1:$G$28)),ROW()-ROW($G$30))))

Hope you don't mind if they're in reverse order. If you do, well, you have homework for the weekend

Regards!

PS: BTW, next time please upload a sample file instead of posting values not aligned to its columns.

PS2: BTW, next time please include examples that are suitable to reflect what you're trying to achieve, so as we don't have to create new entries.

cacos · Jan 12, 2013

Thanks a lot SirJB! Sorry I didn't post a sample file. What you posted is exactly what I had in mind. My only concern is, I was using a similar array formula, and I found that they struggle when going through a large dataset. And that's my case, I'm using thousands of rows and it takes some time calculating, that's why I thought that a VBA approach might make things run faster.

Thank you for your time, much appreciated.

SirJB7 · Jan 14, 2013

Hi, cacos!

Ok, you win, for large amount of data it might last some time to calculate, that's the con of volatile functions. I'll try to come back later with a VBA solution.

Regards!

SirJB7 · Jan 15, 2013

Hi, cacos!

Give a look at this file:

https://dl.dropbox.com/u/60558749/Macro%20to%20filter%20text%20based%20on%20multiple%20criteria%20%28for%20cacos%20at%20chandoo.org%29.xlsm

It's the same as previous one but with:

a) filtered data by VBA code in column H

b) automatic trigger in cell J30 (Y, each time you modify a parameter it rebuilds the list; N, you should rebuild it manually running macro DoTheJob)

c) 4 named ranges: SourceTable, TargetList, ParameterSearch, AutomaticFilter

The code is this:

-----

[pre]

Code:

Option Explicit

&#39;constants
Const gksSourceTable = &#34;SourceTable&#34;
Const gksTargetList = &#34;TargetList&#34;
Const gksParameterSearch = &#34;ParameterSearch&#34;
Const gksAutomaticFilter = &#34;AutomaticFilter&#34;
Const gksY = &#34;Y&#34;

&#39; declarations
Dim rngST As Range, rngTL As Range, rngCR As Range, rngAF As Range

Private Sub Worksheet_Change(ByVal Target As Range)
&#39; constants
&#39; declarations
&#39; start
Set rngAF = Range(gksAutomaticFilter)
If rngAF.Value &#60;&#62; gksY Then GoTo Worksheet_Change_Exit_1
&#39; process
Set rngCR = Range(gksParameterSearch)
If Application.Intersect(Target, rngCR) Is Nothing Then GoTo Worksheet_Change_Exit_2
DoTheJob
DoEvents
&#39; end
Beep
Worksheet_Change_Exit_1:
Set rngAF = Nothing
Worksheet_Change_Exit_2:
Set rngCR = Nothing
End Sub

Sub DoTheJob()
&#39; constants
&#39; declarations
Dim I As Long, J As Long, K As Long, B As Boolean
&#39; start
Application.EnableEvents = False
Set rngST = Range(gksSourceTable)
Set rngTL = Range(gksTargetList)
Set rngCR = Range(gksParameterSearch)
Set rngAF = Range(gksAutomaticFilter)
With rngTL
If .Rows.Count &#62; 1 Then
Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End If
End With
&#39; process
K = 1
With rngST
For I = 2 To .Rows.Count
B = True
For J = 1 To rngCR.Columns.Count
If .Cells(I, J).Value &#60;&#62; rngCR.Cells(1, J).Value Then
B = False
Exit For
End If
Next J
If B Then
K = K + 1
rngTL.Cells(K, 1).Value = .Cells(I, J).Value
End If
Next I
End With
&#39; end
Set rngAF = Nothing
Set rngCR = Nothing
Set rngTL = Nothing
Set rngST = Nothing
Application.EnableEvents = True
End Sub

[/pre]
-----

Just advise if any issue.

Regards!

cacos · Jan 15, 2013

Its perfect!

What if I wanted to have the option to select "All" in any of the parameters? So that it would have to ignore that column I guess?

Thank you for your help, I've been having a huge headache with sorting text through arrays

SirJB7 · Jan 15, 2013

Hi, cacos!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

And regarding your last and new what-if wish, what if you try to adapt the code to process "All" or blank options?

A clue? This is the related statement:

-----

Code:

If .Cells(I, J).Value &#60;&#62; rngCR.Cells(1, J).Value Then

-----

Regards!

cacos · Jan 15, 2013

Haha great! I'll be working on figuring that one. Thanks for the clue. VBA is just awesome

Macro to filter text based on multiple criteria

cacos

Member

cacos

Member

SirJB7

Excel Rōnin

cacos

Member

SirJB7

Excel Rōnin

cacos

Member

SirJB7

Excel Rōnin

SirJB7

Excel Rōnin

cacos

Member

SirJB7

Excel Rōnin

cacos

Member