Results 1 to 4 of 4

Thread: Copy matching values in range in two columns into another column

  1. #1
    Seeker technik's Avatar
    Join Date
    Oct 2018
    Posts
    12
    Articles
    0
    Excel Version
    MS 2016

    Copy matching values in range in two columns into another column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    New here and still learning . seem to have hit a brick wall so back to the board.

    I have tried amending some previous code to give me a different output. Seemed simple enough but I am not getting the desired output and I'm also not sure it's the most efficient method. I think I am making a small error somewhere?

    Basically I have two columns and all I am trying to do is copy any matching/duplicate names in both columns to a new column J. Column I is simply numbered 1,2,3, etc and is dynamically generated.

    The number of candidates in both columns can of course change and is not fixed.

    I've attached my file and would appreciate any help. I can do it in Excel but would like a Excel VBA script to do it.

    Many thanks in advance.
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    How about
    Code:
    Dim aName As Range, bName As Range, cel As Range, writerow As Long
    
    Set aName = Range("B4", Range("B" & Rows.Count).End(xlUp))
    Set bName = Range("F4", Range("F" & Rows.Count).End(xlUp))
    writerow = 4    ' row to start writing to
    
    For Each cel In aName
        If WorksheetFunction.CountIf(bName, cel.Value) > 0 Then
            Range("J" & writerow).Value = cel.Value
            writerow = writerow + 1
        End If
    Next cel

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    768
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Dam, too late to delete.
    Ignore the above, you're a cross poster.

  4. #4
    Seeker technik's Avatar
    Join Date
    Oct 2018
    Posts
    12
    Articles
    0
    Excel Version
    MS 2016
    Not exactly what I wanted but thanks for your help NoS.

    I may have asked for help elsewhere, didn't realise it was an issue? Apologies if I have broken any rules but a genuine oversight if I have.

    Thanks again
    Last edited by technik; 2019-06-20 at 04:01 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •