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]

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.

Did you delete the equal sign as suggested?

 

[ke6zoy]

Did you delete the equal sign as suggested?

I replaced any instance of = with equals. and the error remains.

 

[ke6zoy]

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

Momentman,
The one item I'm having trouble with in your script is that it puts a blank line with carriage return at the top of each cell before placing data in the cell. (just a formatting issue).
I understand how your code works up to the point after you link wks2 as the active sheet (Set wks2 = ActiveWorkbook.ActiveSheet). Would you be able to help explain how the rest is functioning?
Thanks!

 

[Momentman]

Does the SUBSTITUTE line not take care of that?

 

[ke6zoy]

Does the SUBSTITUTE line not take care of that?

Ok, I don't know what happened the first few times i ran it. but i decided to close out of excel and re-launch and now everything works properly!

Thanks.

Would you be able to explain what is taking place in the following portion of the code? I realize that's where all of the "magic" is happening however it's not seeming intuitive to me. i'm also fairly new with macros and trying to learn how things are done. Thanks!

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

 

[Momentman]

You understand the first part of the code which is to get a unique list of names after removing all duplicates

like

Beth
Carlos
David
Edward
mary

We have 2 counters(i and j) i is the row number on the unique list sheet while j is the row counter on the original sheet(the one you initially posted)

For i = 1 To wks2.Range("A" & Rows.Count).End(xlUp).Row

This line shows a for -loop where I is the row number, it performs operations on each row starting from row 1 to the last non-empty row

Now what you need to do for example if "Beth" is the first name, is to loop from row to row checking if the values in the original data equal beth, that's

If wks.Range("A" & j).Value = wks2.Range("A" & i).Value Then

If that's true for a particular row, then get the value in column B fromthe original sheet to the new/summarized/unique list sheet, thats this line

wks2.Range("B" & i).Value = a & Chr(10) & wks.Range("B" & j).Value

a is initally blank, so for the first entry found you concatenate a with the value you obtained but in-between, you put a chr(10) which represents a newline within a cell or equivalent to pressing alt+enter

you now set the value of a to the newly obtained exprression, when another match is found it concatenates what it previously had with the new value obtained and so on

The last line was just to get rid of the initially carriage return at the beginning.

I used to have a way to avoid that carriage return, but right now I can't remember, so i just used the substitute to finally handle that

My explanations may not have been so coherent but if you have specific questions, you can ask, and i would gladly explain, at least based on my knowledge

Victor

 

[ke6zoy]

thanks, that helps a lot!