| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match Values
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Values
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 "K" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Values
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 "Mike H" wrote: 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 "K" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Values
On Jan 5, 10:48*am, Mike H wrote:
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 "K" wrote: 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.- Hide quoted text - - Show quoted text - Thats it, thats what i was looking for. Thanks lot Mike |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Values
Glad I could help
"K" wrote: On Jan 5, 10:48 am, Mike H wrote: 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 "K" wrote: 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.- Hide quoted text - - Show quoted text - Thats it, thats what i was looking for. Thanks lot Mike |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Match Values
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 "K" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match formula to match values in multiple columns | Excel Discussion (Misc queries) | |||
| Match several values in Summary Sheet to several values in Mast | Excel Programming | |||
| how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
| Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
| How do i compare values from two sheet and copy & paste if values match? | Excel Programming | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
