How do I lookup several values and concatenate the results?
in [General]
|
Prev: How do I set the default for task bar in Excel
Next: How do I stop 'template help' from opening in a worksheet?
From: Jane on 28 May 2010 20:38 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"
From: Dave Peterson on 28 May 2010 21:43
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 here: 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 |