Match Values

[K]

Hi all, I have codes in column A like (see below)
A ……….col
400601
401803
401820

and then I have codes and their detail in column E and F
E F……col
400601 Stationery
400601 Stationery
400601 Systems Desk Diary Refill
400601 Sellotape. Wrong Size.
401803 Sellotape
401803 Flip chart pads
401820 Milk pots 530897
401820 Laser paper 530897

I want a macro on a button which should produce result in column B
something like this (see below)
A B……..col
400601 Stationery , Stationery , Systems Desk Diary Refill ,
Sellotape. Wrong Size.
401803 Sellotape , Flip chart pads
401820 Milk pots 530897 , Laser paper 530897

basically in other words what I want macro to do is lets say if I have
code "400601" (as shown above) in column A then macro should check
that code in column E and if it match then check what is the detail in
column F next to that matched code in column E and then put that
detail in column B next to code "400601". If matched codes in column
E are more than one then macro should joint all details which are
coming in column F against that code and put comma between each detail
and then put that in column B. I hop i was able to explain my
question. Please can any friend can help.

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub Stantial()
Dim MyRange1 As Range, MyRange2 As Range
Dim Lastrow As Long
Dim MyString As String
Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange1 = Range("A1:A" & Lastrow1)
Lastrow2 = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange2 = Range("E1:E" & Lastrow2)
For Each c In MyRange1
For Each d In MyRange2
If c.Value = d.Value Then
MyString = MyString & d.Offset(, 1).Value & " , "
End If
Next
c.Offset(, 1).Value = MyString
MyString = ""
Next
End Sub

Mike

 

[Bob Phillips]

Public Sub ProcessData()
Dim LastRow As Long
Dim i As Long
Dim cell As Range
Dim tmp As String

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

tmp = ""
For Each cell In Range(.Range("E1"), .Range("E1").End(xlDown))

If cell.Value = .Cells(i, "A").Value Then

tmp = tmp & cell.Offset(0, 1).Value & ","
End If
Next cell

.Cells(i, "B").Value = Left$(tmp, Len(tmp) - 1)
Next i
End With

End Sub

--
__________________________________
HTH

Bob

Hi all, I have codes in column A like (see below)
A ……….col
400601
401803
401820

and then I have codes and their detail in column E and F
E F……col
400601 Stationery
400601 Stationery
400601 Systems Desk Diary Refill
400601 Sellotape. Wrong Size.
401803 Sellotape
401803 Flip chart pads
401820 Milk pots 530897
401820 Laser paper 530897

I want a macro on a button which should produce result in column B
something like this (see below)
A B……..col
400601 Stationery , Stationery , Systems Desk Diary Refill ,
Sellotape. Wrong Size.
401803 Sellotape , Flip chart pads
401820 Milk pots 530897 , Laser paper 530897

basically in other words what I want macro to do is lets say if I have
code "400601" (as shown above) in column A then macro should check
that code in column E and if it match then check what is the detail in
column F next to that matched code in column E and then put that
detail in column B next to code "400601". If matched codes in column
E are more than one then macro should joint all details which are
coming in column F against that code and put comma between each detail
and then put that in column B. I hop i was able to explain my
question. Please can any friend can help.

 

[Mike H]

Just noticed I lkeft a comma at the end, try this small change

Sub Stantial()
Dim MyRange1 As Range, MyRange2 As Range
Dim Lastrow As Long
Dim MyString As String
Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange1 = Range("A1:A" & Lastrow1)
Lastrow2 = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange2 = Range("E1:E" & Lastrow2)
For Each c In MyRange1
For Each d In MyRange2
If c.Value = d.Value Then
MyString = MyString & d.Offset(, 1).Value & " , "
End If
Next
c.Offset(, 1).Value = Left(MyString, Len(MyString) - 3)
MyString = ""
Next
End Sub

Mike

 

[K]

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub Stantial()
Dim MyRange1 As Range, MyRange2 As Range
Dim Lastrow As Long
Dim MyString As String
Lastrow1 = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange1 = Range("A1:A" & Lastrow1)
Lastrow2 = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange2 = Range("E1:E" & Lastrow2)
For Each c In MyRange1
    For Each d In MyRange2
        If c.Value = d.Value Then
            MyString = MyString & d.Offset(, 1).Value & " ,"
        End If
Next
c.Offset(, 1).Value = MyString
MyString = ""
Next
End Sub

Mike








- Show quoted text -

Thats it, thats what i was looking for. Thanks lot Mike

 

[Mike H]

Glad I could help

Thats it, thats what i was looking for. Thanks lot Mike