Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make the results of a concatenate permanent text? | Excel Worksheet Functions | |||
CONCATENATE not producing any results | Excel Worksheet Functions | |||
CONCATENATE not producing any results | Excel Worksheet Functions | |||
Problem with Concatenate - Results are too long for CSV | Excel Worksheet Functions | |||
I can't get my concatenate formula results to show | Excel Discussion (Misc queries) |