If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I lookup several values and concatenate the results?
I have data that comes out of the system of record like this.
Item # Description 1234 Widget 1234 Blue 1234 Large 2345 Thingy 2345 Purple 3456 Gizmo 3456 Red 3456 Large 3456 Square 3456 With Buttons How do I combine the variable number of descriptions into one field for each item #? Each item doesn't have the same amount of descriptions. I want the output to be like this. Item # Description 1234 "Widget,Blue,Large" 2345 "Thingy,Purple" 3456 "Gizmo,Red,Large,Square,With Buttons" |
#2
|
|||
|
|||
How do I lookup several values and concatenate the results?
You could use a macro.
I'm guessing that you really didn't want the double quotes around the longer description. Option Explicit Sub testme() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 2 'headers in row 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow - 1, "A").Value = .Cells(iRow, "A").Value Then 'same group .Cells(iRow - 1, "B").Value _ = .Cells(iRow - 1, "B").Value _ & "," & .Cells(iRow, "B").Value .Rows(iRow).Delete End If Next iRow End With End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Jane wrote: I have data that comes out of the system of record like this. Item # Description 1234 Widget 1234 Blue 1234 Large 2345 Thingy 2345 Purple 3456 Gizmo 3456 Red 3456 Large 3456 Square 3456 With Buttons How do I combine the variable number of descriptions into one field for each item #? Each item doesn't have the same amount of descriptions. I want the output to be like this. Item # Description 1234 "Widget,Blue,Large" 2345 "Thingy,Purple" 3456 "Gizmo,Red,Large,Square,With Buttons" -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|