# Efficient sorting of data with VBA with 3 criteria

#### Darthrocket

##### New Member
Hi Mr. Excel.

I am in the process of automating some stuff at work, and therefore I would like to sort some data with VBA, based on 3 criteria.

I would like so sort some data like seen below: The way I am doing it now is like the following code:

Code:
``````Range("G7").Select
Selection.FormulaArray = "=INDEX(\$E\$7:\$E\$" & LastRow & ",MATCH(\$G7&H\$6&\$G\$6, \$D\$7:\$D\$" & LastRow & "&\$B\$7:\$B\$" & LastRow & "&\$C\$7:\$C\$" & LastRow & ",0))"
Selection.AutoFill Destination:=Range("G7:F" & LastRow2)``````
However this is terribly inefficient, and takes forever to calculate.

Unfortunately my VBA skill are not good enough yet, to figure out a solution, so I hope that you can help me.

Thanks!

#### Kenneth Hobson

##### Well-known Member
Since Sample place is the first sort, I don't see how you deal with the next Sample place.

#### Darthrocket

##### New Member
Maybe I should have mentioned it, but first I sort for unique batchnumbers:

Code:
``````Range("D7:D" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"G7"), Unique:=True``````
Then I use the following formula (I will insert the names to hopefully make it easier to read): {=INDEX(Values, MATCH(Batchnumber&Impurity&SamplePlace;Batchnumber_range&Impurity_range&SamplePlace_range;0))}

Then I copy this formula down.

For now I am only interested in sample place 1.

#### Darthrocket

##### New Member
For anyone interested, I came up with the following solution, that is somewhat quicker. Note the cellrefenrences may not align with the example I posted above.

Code:
``````Option Explicit

Sub FilterUniqueBatchnumber()
Dim LastRowFrom As Long

LastRowFrom = ThisWorkbook.Worksheets("Data").Range("C1").End(xlDown).Row

Range("C1:C" & LastRowFrom).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"L1"), Unique:=True

End Sub``````
Code:
``````Sub Sort(ImpurityColumn As Variant)

Dim i As Long
Dim j As Long
Dim LastRow As Long

LastRow = ThisWorkbook.Worksheets("Data").Range("C1").End(xlDown).Row

j = 2
For i = 2 To LastRow

If Range("A" & i) = Range(ImpurityColumn & "1") And Range("E" & i) = Range("K2") And Range("C" & i) = Range("L" & j) Then

Range(ImpurityColumn & j) = Range("B" & i)
j = j + 1
End If

Next i
End Sub``````
Code:
``````Sub RunSort()

FilterUniqueBatchnumber

Call Sort("M")
Call Sort("O")

End Sub``````

Still any improvements are welcome!

### This Week's Hot Topics

• Get External Data (long shot question!)
This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
• Importing multiple excel files into one spreadsheet
Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
• Cell Formatting
Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
• How to copy multiple rows using If
Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
• Workbook_Change stopped working !
I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
• VBA If statement
Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...