CODEWITHSUNDEEP
excelvba
Anthony
Anthony

Reputation: 552

How to filter data in one column then obtain a value in another column that is linked to filtered the data

I have a database of +- 15000 lines. In column U there are model codes that I need to filter. In column H I have a list of unique asset numbers. Multiple models each with a unique asset number. So I need one of the unique asset numbers in Column H for each filtered model code which is then copied and pasted in Column A Sheet2 I am able to filter with the code below but how do I obtain the one unique value in Column H?

Sub SumGroups()
    Dim lastCode As Long, lastFiltCode As Long

    'Determine Last Row in Column U (Unfiltered Codes)
     With Worksheets("Database") lastCode = .Range("U" & .Rows.Count).(xlUp).Row

    'Filter Unique Codes into Column A Sheet2
    .Range("U2:U" & lastCode).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=True
    End With
End Sub

Upvotes: 0

Views: 42

Answers (1)

Gangula
Gangula

Reputation: 7324

Although I would say the simplest way to go about this is using a Pivot Table, below is the solution to your code if you need a solution using VBA.

You can use RemoveDuplicates function after copying the data to Sheet2 to keep only unique values like below.

I haven't checked if this works yet, but from what I can see it should.

Dim lastCode As Long, lastFiltCode As Long

'Determine Last Row in Column U (Unfiltered Codes)
 With Worksheets("Database") lastCode = .Range("U" & .Rows.Count).(xlUp).Row

'Filter Unique Codes into Column A Sheet2
.Range("U2:U" & lastCode).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=True
End With
'Use Header:=xlNo below is Sheet2 doesn't have header
Worksheets("Sheet2").Range("A1:A100").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Please Change the Range("A1:A100") above to your respective range in Sheet2.

Upvotes: 1

Related Questions