Hmmm...I cannot see it being solved with advanced filter only. My only criteria is unique combinations of columns WF and WT, that's doable. What I cannot do is to get the values of columns ST and ET, because for these columns I have no criteria. I just wanna get the data from them for the first unique combination no matter if they are unique or not.
Anyway, I kind of solved my problem for now but I suppose there is a faster way. What I have done is: Filter only the first 2 columns and copy them to another sheet, then I have created a loop in VBA that takes the unique combinations, finds them in the original database, picks the values from the last 2 columns and exits the loop.
Worksheets("1").Range("B:C").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="" _
, CopyToRange:=Worksheets("2").Range("A1:B100"), Unique:=True
For i = 2 To Worksheets("2").Cells(Rows.Count, "A").End(xlUp).Row
WFUNIQUE(i) = Worksheets("2").Cells(i, 1)
WTUNIQUE(i) = Worksheets("2").Cells(i, 2)
Next i
For j = 2 To Worksheets("2").Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To Worksheets("1").Cells(Rows.Count, "A").End(xlUp).Row
If WFUNIQUE(j) = WF(i) And WTUNIQUE(j) = WT(i) Then
Worksheets("2").Cells(j, 3) = ST(i)
Worksheets("2").Cells(j, 4) = ET(i)
Exit For
End if
Next i
Next j
Well, if there is nothing faster to suggest let's consider it solved ;-)
Bookmarks