Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate info from columns into rows
Hie, can anyone help with the following:
COL A COL B COL C COL D Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH Unit 4 Unit 2 4B Unit 4 Unit 2 6LH Unit 2 7RH Unit 3 5B Unit 3 4LH Unit 3 9RH Unit 4 What i've got are 4 unique units, each of which have a handing assigned to them. I need to concatenate the info in each column against each unique unit, which gives the info on one row for each unit (COL D). This is just an example. I may have unique units which have no handing info against them (e.g. Unit 4) and therefore will appear on there own in COL D with no handing info against them etc. A macro to automate this process would be greatly appreciated. Thanks -- jj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate info from columns into rows
Sub combinerows()
RowCountColA = 1 RowCountColD = 1 Do While Range("A" & RowCountColA) < "" UnitNo = Range("A" & RowCountColA) RowCountColB = 1 CombinedString = UnitNo & ": " MyString = CombinedString Do While Range("B" & RowCountColB) < "" If Range("B" & RowCountColB) = UnitNo Then 'need to see if init string so a comma can be added If MyString = CombinedString Then MyString = MyString & Range("C" & RowCountColB) Else MyString = MyString & "," & Range("C" & RowCountColB) End If End If RowCountColB = RowCountColB + 1 Loop RowCountColA = RowCountColA + 1 Range("D" & RowCountColD) = MyString RowCountColD = RowCountColD + 1 Loop End Sub "jbjtc" wrote: Hie, can anyone help with the following: COL A COL B COL C COL D Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH Unit 4 Unit 2 4B Unit 4 Unit 2 6LH Unit 2 7RH Unit 3 5B Unit 3 4LH Unit 3 9RH Unit 4 What i've got are 4 unique units, each of which have a handing assigned to them. I need to concatenate the info in each column against each unique unit, which gives the info on one row for each unit (COL D). This is just an example. I may have unique units which have no handing info against them (e.g. Unit 4) and therefore will appear on there own in COL D with no handing info against them etc. A macro to automate this process would be greatly appreciated. Thanks -- jj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate info from columns into rows
Thanx Joel
It works a treat -- jj "Joel" wrote: Sub combinerows() RowCountColA = 1 RowCountColD = 1 Do While Range("A" & RowCountColA) < "" UnitNo = Range("A" & RowCountColA) RowCountColB = 1 CombinedString = UnitNo & ": " MyString = CombinedString Do While Range("B" & RowCountColB) < "" If Range("B" & RowCountColB) = UnitNo Then 'need to see if init string so a comma can be added If MyString = CombinedString Then MyString = MyString & Range("C" & RowCountColB) Else MyString = MyString & "," & Range("C" & RowCountColB) End If End If RowCountColB = RowCountColB + 1 Loop RowCountColA = RowCountColA + 1 Range("D" & RowCountColD) = MyString RowCountColD = RowCountColD + 1 Loop End Sub "jbjtc" wrote: Hie, can anyone help with the following: COL A COL B COL C COL D Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH Unit 4 Unit 2 4B Unit 4 Unit 2 6LH Unit 2 7RH Unit 3 5B Unit 3 4LH Unit 3 9RH Unit 4 What i've got are 4 unique units, each of which have a handing assigned to them. I need to concatenate the info in each column against each unique unit, which gives the info on one row for each unit (COL D). This is just an example. I may have unique units which have no handing info against them (e.g. Unit 4) and therefore will appear on there own in COL D with no handing info against them etc. A macro to automate this process would be greatly appreciated. Thanks -- jj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate multiple rows and columns into 1 cell | Excel Discussion (Misc queries) | |||
Columns A - BY and 336 rows.Too much to filter&sort?Lose info. | Excel Worksheet Functions | |||
Columns A - BY and 336 rows.Too much to filter&sort?Lose info. | Excel Worksheet Functions | |||
Numbering rows in A1 only no matter info in other columns. | New Users to Excel | |||
Rearrange info in rows to columns - HELP! | Excel Discussion (Misc queries) |