Concatenating Problem
Hello,
I have an Excel sheet with a header row containing the following fields: Customer ID | Numeric Response The rows beneath contain data as such: 5 | 1 5 | 4 5 | 7 7 | 2 7 | 3 8 | 1 Basically Customer ID "5" answered 1,4, and 7 and has a separate row for each answer (same with Customer ID "7" answering 2 and 3, etc...) I would like to concatenate the Response row into one field so that ultimately I have one column that looks like: Customer ID | Numeric Response 5 | 1 4 7 7 | 2 3 8 | 1 etc.... Thank you in advance, Jack |
Concatenating Problem
You may use a macro like this:
Sub ConcatenateID() Dim rngOrig, rngDest As Range Dim i, d, a, Values Set d = CreateObject("Scripting.Dictionary") 'This is the starting range of the current data Set rngOrig = Worksheets("Sheet3").Range("A1") 'This is the starting range of the destination Set rngDest = Worksheets("Sheet3").Range("C1") i = 0 'Gather the information, loop until we find a blank Do While rngOrig.Offset(i, 0).Value < "" Values = Split(rngOrig.Offset(i, 0).Value, " | ") If d.exists(Values(0)) Then d(Values(0)) = d(Values(0)) & " " & Values(1) Else d.Add Values(0), Values(1) End If i = i + 1 Loop 'Print the information in the destination range a = d.keys For i = 0 To d.Count - 1 rngDest.Offset(i, 0).Value = a(i) & " | " & d(a(i)) Next End Sub Hope this helps, Miguel. "Jack" wrote: Hello, I have an Excel sheet with a header row containing the following fields: Customer ID | Numeric Response The rows beneath contain data as such: 5 | 1 5 | 4 5 | 7 7 | 2 7 | 3 8 | 1 Basically Customer ID "5" answered 1,4, and 7 and has a separate row for each answer (same with Customer ID "7" answering 2 and 3, etc...) I would like to concatenate the Response row into one field so that ultimately I have one column that looks like: Customer ID | Numeric Response 5 | 1 4 7 7 | 2 3 8 | 1 etc.... Thank you in advance, Jack |
All times are GMT +1. The time now is 02:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com