comparing two columns of data 1

[star1geo]

I'm trying to take two columns of data (one is 500 entries the other is 1500 entries) and compare and generate exact matches as column C. I've tries using the VLOOKUP function, but can't seem to get the formula to work.

 

[pauldes]

I might not be fully understanding the problem but if your looking for column C to show the value if column A and B are equal in columns B and C, why not use =IF(A1=B1,A1,"&quot. If I'm way off, maybe rephrase the problem.

Good Luck.

 

[Sareimer]

If your data is in Columns A & B then the formula should look something like this in Column C:
=VLOOKUP(A1:A500,Name,1)

But first you should name your range in Column B. Highlight the selection in Column B then click on Insert-Name-Define. Type a Name you will remember, then OK. Use the name you just defined in the formula above.

The only bad thing about this formula is it will not tell you where your matches in Column B are (ie Column B Row 21). It will only verify that there is in fact an exact match between columns A & B.

Hope this helps.

 

[AlexIT]

Depends on what you want as result, looking for identical matches one-to-one, Pauldes gave a good answer. I haven't used Vlookup too much but I write lots in VB. You can try this:

Sub Compare()

'Identify arrays

Dim aNoA() As String
Dim aNoB() As String

u = 1500 'Number of rows expected in Column A
v = 500 'Number of rows expected in Column B

ReDim aNoA(u) As String
ReDim aNoB(v) As String

'Set counters

h = 1
i = 1

'Fill arrays with data

For y = 1 To u
aNoA = ThisWorkbook.Sheets(1).Cells(y, 1).Value
Next y

For x = 1 To v
aNoB(x) = ThisWorkbook.Sheets(1).Cells(x, 2).Value
Next x

'Compare short column to long (one-to-many searching)
For x = 1 To u
j = h
For y = 1 To v
'Find a match write it in Column C
If aNoA(x) = aNoB Then
ThisWorkbook.Sheets(1).Cells(h, 3) = aNoB(x)
h = h + 1
End If
Next y
'When no matches write the number in Column D
If j = h Then
ThisWorkbook.Sheets(1).Cells(i, 4) = aNoB(x)
i = i + 1
End If
Next x

End Sub


Alex