Associating two different cells

Closed
andy - Jan 21, 2010 at 02:18 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 21, 2010 at 09:25 PM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 21, 2010 at 09:25 PM
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
0