• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba how to copy data from one column by filtering each element from another column

joelmolina37

New Member
Hi All,

(Edit) New to the VBA world I am trying to make a vba script that filters each element from (checklist details) column B copies the result from column C removes duplicates and paste the name of the ref with hostname in another sheet. Thanks to anyone that can help.
 

Attachments

  • example.xlsx
    913.3 KB · Views: 6
Last edited:
According to your last attachment a VBA demonstration for starters :​
Code:
Sub Demo1()
        V = Sheet1.[A1].CurrentRegion.Columns("B:C")
        Sheet2.UsedRange.Offset(1).ClearContents
    With CreateObject("Scripting.Dictionary")
        For R& = 2 To UBound(V)
            S$ = .Item(V(R, 1))
            T$ = " " & V(R, 2) & " "
            If InStr(S, T) = 0 Then .Item(V(R, 1)) = S & T
        Next
            V = Application.Transpose(Array(.Keys, .Items))
           .RemoveAll
    End With
    With Sheet2.[A2:B2].Resize(UBound(V))
        .Borders.Weight = 2
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Value = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
>>> use code - tags <<<
Code:
Sub STEP2()
    Dim V As Variant
    Dim R As Long
    Dim S As String
    Dim T As String
   
    V = Sheets("Checklist Details").Range("A1").CurrentRegion.Columns("B:C")
    Sheets("STIG HOST").UsedRange.Offset(1).ClearContents
   
    With CreateObject("Scripting.Dictionary")
        For R = 2 To UBound(V)
            S = .Item(V(R, 1))
            T = " " & V(R, 2) & " "
            If InStr(S, T) = 0 Then .Item(V(R, 1)) = S & T
        Next
       
        V = Application.Transpose(Array(.Keys, .Items))
        .RemoveAll
    End With
   
    With Sheets("STIG HOST").Range("A2").Resize(UBound(V), 2)
        .Borders.Weight = 2
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Value = V
    End With
End Sub


this was my edit so it can call to the certain sheet on the doc thanks for all this was great.
 
Last edited by a moderator:
This is the sort of thing that Power Query (built in to Excel) can do without VBA.
See the table in cell D1 of the EXAMPLE OF OUTPUT REQUESTED sheet of the attached workbook.
It updates (by refreshing) like you would a pivot table after changing the data in the source table.
 

Attachments

  • Chandoo51773Example.xlsx
    933.8 KB · Views: 3
Back
Top