Copy matching values in range in two columns into another column

T

technik

New member
Joined
Oct 8, 2018
Messages
13
Reaction score
0
Points
0
Excel Version(s)
MS 2016
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.​
 

Attachments

  • bsn.xlsm
    22.7 KB · Views: 8
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
 
Dam, too late to delete.
Ignore the above, you're a cross poster.
 
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:
You must log in or register to reply here.
Back
Top