Associating two different cells

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
I am trying to find a formula that will allow me to associate two different columns. Example Sheet1
A B
Jim 5
Bob 3
Jim 2

On another sheet I want to be able the cells to associated the name in column A with the number in column B. I want sheet to look like the example below.
Sheet 2
A B C
Jim 5 2
Bob 3

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
your data is like this (heading are necessary)

from A1 to B4

NAME DATA
Jim 5
Bob 3
Jim 2

now run this macro and see whether you get what you want

Sub TEST()
Dim cfind As Range, add As String
Dim r As Range, dest As Range, ru As Range, c As Range
Worksheets("sheet1").Activate
On Error Resume Next
Set r = Range(Range("A1"), Range("A1").End(xlDown))
Set dest = Cells(Rows.Count, "A").End(xlUp).Offset(5, 0)
r.AdvancedFilter xlFilterCopy, , dest, True
Set ru = Range(dest.Offset(1, 0), dest.End(xlDown))
For Each c In ru
Set cfind = r.Cells.Find(what:=c.Value, lookat:=xlWhole)
add = cfind.Address
c.Offset(0, 1) = cfind.Offset(0, 1)
Do
Set cfind = r.Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
Cells(c.Row, Columns.Count).End(xlToLeft).Offset(0, 1) = cfind.Offset(0, 1)
Loop
Next
End Sub