Find which stock #s are on both spreadsheets Closed

Question

Hello, 

I have two excel spreadsheets.  I want to know how I can find out which stock numbers are on both spreadsheets.

Do I use VLOOKUP and if so how do I write the formula?

Thanks

vcoolio · Answer

Hello Dawn,

Perhaps using a VBA code to compare the two sheets would be a good option, as follows:-


Sub FindStockNos()

Dim lRow As Long
    Dim fValue As Range

Sheet1.Select

lRow = Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Range("H2:H" & lRow).ClearContents

For Each cell In Range("A2:A" & lRow)
    Set fValue = Sheet2.Columns("A:A").Find(cell.Value)
    If fValue Is Nothing Then GoTo NextCell
    If cell.Value = fValue.Value Then
        Range(cell, cell.Offset(0)).Copy Sheet1.Range("H" & Rows.Count).End(xlUp).Offset(1)
    End If
NextCell:
Next cell

End Sub

Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/pctzuscckwtnyvb/Dawn%28Compare%20stock%20nos.%20in%202%20sheets%29.xlsm?dl=0


Click on the orange button and the stock numbers which are on both sheets will appear in Sheet1, Column H.

Let us know if you need help to implement the code.

I hope that this helps.

Cheerio,
vcoolio.

Find which stock #s are on both spreadsheets

1 response

Similar discussions

Subscribe To Our Newsletter!