combine 2columns

[sna]

Hi Dear

I need your help how to check item between lista and listb then combine into a column for matching items.

I attached a
tks

[snasui]

In cell C2 enter this formula,

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$10)/ISNUMBER(MATCH($B$2:$B$10,$A$2:$A$7,0)),ROWS(C$2:C2))),"")

Enter and copy down.

[sna]

Thank you

[sna]

Code: Select all

Sub test()
Dim i As Long,Lr As Long 
Dim rng As Range,C As Range 
Dim r
Lr= Sheet1.Range("O" & Rows.Count).End(xlUp).Row 
Set rng=Sheet1.Range("O2:O"&Lr)
For Each C in rng.Cells
Set r= Sheet1.Range("U:U").Find(What:=C.Value,Lookin:=xlValues,Look at:=xlWhole)
If Not r is Nothing Then
Range("W" & Rows.Count).End(xlUp).Offset(1).Value = Cells(r.Row,"U")
End If 
Next 
End Sub

[sna]

Hi Dear
the formula working but I come up with new idea to write code

[snasui]

Please attach code in the example file and explain what the problem is.

[sna]

Hi Dear,
the code above is aimed to populate data like the formula you gave but no luck.what's wrong?

[snasui]

I don't find any error in that code.

[sna]

thanks,I will look at it again,may it coz of sheet name