How to Combine Multiple rows of data into Single Row.

[ke6zoy]

Hi All, I've been searching and searching and haven't found anything that quite fits my needs besides some 3rd party software which will be a complete headache to get my IT department to install.
I've simplified my scenario as my data is dynamic and goes on for hundreds/thousands of rows.
Below is an example of 2 columns of data and in my worksheet.
When I import data it puts a seperate row for each piece of information associated with the user. I'd like all of the users information combined into a single cell (so that there is only 1 row associated with each user).
[TABLE="width: 81"]
<colgroup><col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <tbody>[TR]
[TD="class: xl68, width: 53, bgcolor: transparent"]Name[/TD]
[TD="class: xl69, width: 55, bgcolor: transparent"]Info[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Beth[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Carlos[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Carlos[/TD]
[TD="class: xl66, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Carlos[/TD]
[TD="class: xl66, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]David[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]David[/TD]
[TD="class: xl66, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Edward[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Frank[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Mary[/TD]
[TD="class: xl66, bgcolor: transparent"]E[/TD]
[/TR]
</tbody>[/TABLE]

This is an example of the end result i'm trying to get to.
(i do not want to use the merge and center type of function because the final resulting data needs to be processed on in ways that don't like merged cells.)
[TABLE="width: 81"]
<colgroup><col style="width: 40pt; mso-width-source: userset; mso-width-alt: 1938;" width="53"> <col style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;" width="55"> <tbody>[TR]
[TD="class: xl66, width: 53, bgcolor: transparent"]Name[/TD]
[TD="class: xl67, width: 55, bgcolor: transparent"]Info[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Beth[/TD]
[TD="class: xl69, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Carlos[/TD]
[TD="class: xl70, width: 55, bgcolor: transparent"]A
B
C[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]David[/TD]
[TD="class: xl70, width: 55, bgcolor: transparent"]A
B[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Edward[/TD]
[TD="class: xl69, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Frank[/TD]
[TD="class: xl69, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Mary[/TD]
[TD="class: xl70, width: 55, bgcolor: transparent"]A
B
C
D
E[/TD]
[/TR]
</tbody>[/TABLE]

I know i need VBA scripting, however i haven't quite figured out the logic to make it all work correctly as the number of rows per user can change from day to day as data is re-imported.

Any help would be greatly appreciated.
Thanks,
-Brian

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Sub[/COLOR] Concatenate_Records()
    
    [COLOR=darkblue]Dim[/COLOR] Data [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Data in colmns A and B[/COLOR]
    [COLOR=green]'Change to suit[/COLOR]
    Data = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
    
    [COLOR=darkblue]With[/COLOR] CreateObject("Scripting.Dictionary")
        
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](Data, 1)
            [COLOR=darkblue]If[/COLOR] .Exists(Data(i, 1)) [COLOR=darkblue]Then[/COLOR]
                .Item(Data(i, 1)) = .Item(Data(i, 1)) & vbLf & Data(i, 2)
            [COLOR=darkblue]Else[/COLOR]
                .Item(Data(i, 1)) = Data(i, 2)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
        
        [COLOR=green]'Output to a new sheet[/COLOR]
        Sheets.Add After:=Sheets(Sheets.Count)
        [COLOR=green]'concatenated data starting at cell A2[/COLOR]
        Range("A2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))
        Columns("A:B").AutoFit
        Columns.VerticalAlignment = xlVAlignTop
        
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[ke6zoy]

Yes, all of the rows that would need to be combined are listed sequentially and in the order that they would need to be consolidated (ie: A needs to appear first, then B, then C etc in the resulting cell).
The A, B, C etc in my example reffers to a string of text data so it's not just an alphanumeric/sequential list.

The origional database that this data is in (actually shows it similar to my desired format) however because the database was developed in house, it does some strange things when exporting it. It imports into excel as seen in my origional post with each piece of info on a seperate row and then fills in the name in column a next to it.

 

[AlphaFrog]

Yes, all of the rows that would need to be combined are listed sequentially and in the order that they would need to be consolidated (ie: A needs to appear first, then B, then C etc in the resulting cell).
The A, B, C etc in my example reffers to a string of text data so it's not just an alphanumeric/sequential list.

The origional database that this data is in (actually shows it similar to my desired format) however because the database was developed in house, it does some strange things when exporting it. It imports into excel as seen in my origional post with each piece of info on a seperate row and then fills in the name in column a next to it.

See post #2 again. I added the code.

 

[ke6zoy]

See post #2 again. I added the code.

I get a Run-time error '13':
Type mismatch


Debug points to the following line just below the if statement:

.Item(Data(i, 1)) = .Item(Data(i, 1)) & vbLf & Data(i, 2)

 

[AlphaFrog]

I get a Run-time error '13':
Type mismatch


Debug points to the following line just below the if statement:

.Item(Data(i, 1)) = .Item(Data(i, 1)) & vbLf & Data(i, 2)

Is this on the example data? Did yo change anything in the code?

What is the value in columns A & B when it errors? The variable i + 1 is the row number. Hover the mouse cursor over i when it errors to see its current value.

 

[ke6zoy]

Is this on the example data? Did yo change anything in the code?

What is the value in columns A & B when it errors? The variable i + 1 is the row number. Hover the mouse cursor over i when it errors to see its current value.

Is this on the example data? Did yo change anything in the code?

What is the value in columns A & B when it errors? The variable i + 1 is the row number. Hover the mouse cursor over i when it errors to see its current value.

So i found the first issue. one of my cells when importing the data from my database had an = sign at the start of the data and it was imported as "general" type and it was attempting to process a function that it didn't understand. i've changed it to "Text" type and now i get the same runtime error but now on the following line:

Range("A2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))

It creates the new worksheet, however no data is populated and cell A1 on sheet 2 (the newly generated sheet) is selected.

 

[AlphaFrog]

So i found the first issue. one of my cells when importing the data from my database had an = sign at the start of the data and it was imported as "general" type and it was attempting to process a function that it didn't understand. i've changed it to "Text" type and now i get the same runtime error but now on the following line:

Range("A2").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .Items))

It creates the new worksheet, however no data is populated and cell A1 on sheet 2 (the newly generated sheet) is selected.

You should delete the leading equal sign.

What is the .Count value when it errors? Hover the mouse cursor over .Count when it errors to see its current value.

 

[Momentman]

I wrote a much longer code but it does work

I assumed your data is in sheet1, change that section of the code to reflect your sheet name

Sub WorkingOut()
    Dim wks As Worksheet
    Dim wks2 As Worksheet
    
    Set wks = Worksheets("Sheet1")
    
    wks.Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    ActiveSheet.Range("$A$1:$A$" & Range("A" & Rows.Count).End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
   
    Set wks2 = ActiveWorkbook.ActiveSheet
    
    For I = 1 To wks2.Range("A" & Rows.Count).End(xlUp).Row
        a = ""
        For j = 2 To wks.Range("A" & Rows.Count).End(xlUp).Row
            If wks.Range("A" & j).Value = wks2.Range("A" & I).Value Then
                wks2.Range("B" & I).Value = a & Chr(10) & wks.Range("B" & j).Value
                a = wks2.Range("B" & I).Value
            End If
        Next j
        wks2.Range("B" & I).Value = WorksheetFunction.Substitute(wks2.Range("B" & I).Value, Chr(10), "", 1)
    Next I
                
    
End Sub

 

[ke6zoy]

Alpha Frog:
When i mouse over the .Count in that line, it doesn't display any reference to the value.
When i mouse over Columns.VerticalAlignment = xlVAlignTop
Columns.VerticalAlignment has a value of -4107 and xlVAlignTop has a value of -4160

Momentman:
Your code appears to work. I'll test it out report back.