Adding multiple rows into one line

stjoe

Board Regular
Joined
Dec 13, 2010
Messages
50
Hello:

I ran a report where there are multiple rows per employee. I need to take the data and show it in one cell.

For example:

I have 4 rows for Jane Doe, in column J I have 4 different types of licesne listed which results in 4 different rows of data. I need to take the 4 rows and combine it to one line so the end result is 1 row of data per employee.

Any assistance would be greatly appreciated.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
On the assumption that your "Names are in column "J" and your license Numbers are in column "K" then this will return what you want in sheet (2).
If you data is different , then please post your sheet.
Code:
[COLOR="Navy"]Sub[/COLOR] MG02Mar43
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("J1"), Range("J" & rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count, 1 To Columns.Count)
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            ray(n, 1) = Dn.value: ray(n, 2) = Dn.Offset(, 1)
            .Add Dn.value, Array(2, ray(n, 1), ray(n, 2), n)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.value)
            Q(0) = Q(0) + 1
            ray(Q(3), Q(0)) = Dn.Offset(, 1)
            .Item(Dn.value) = Q
            oMax = Application.Max(oMax, Q(0))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
Sheets("Sheet2").Range("A1").Resize(.Count, oMax) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
I have a spreadsheet that has my data, I was trying to find a formula that I can add to Column K that will look at the employee then take their rows in Column J and concatinate all into one line in column K.

I dont know where to add the code you sent me.
 
Upvote 0
I copied the code in VB and got an run time error then when I hit the debug I got the following highlighted

ReDim ray(1 To Rng.Count, 1 To Columns.Count)

I am using excel 2007
 
Upvote 0
If your sheet is something like this:-
Code:
[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(J) [/B][/COLOR][COLOR=royalblue][B]Col(K)  [/B][/COLOR]
1.      nam1    L/No 1  
2.      nam2    L/No 2  
3.      nam3    L/No 3  
4.      nam4    L/No 4  
5.      nam5    L/No 5  
6.      nam1    L/No 6  
7.      nam2    L/No 7  
8.      nam3    L/No 8  
9.      nam4    L/No 9  
10.     nam5    L/No 10 
11.     nam1    L/No 11 
12.     nam2    L/No 12 
13.     nam3    L/No 13 
14.     nam4    L/No 14 
15.     nam5    L/No 15 
16.     nam1    L/No 16 
17.     nam2    L/No 17 
18.     nam3    L/No 18 
19.     nam4    L/No 19 
20.     nam5    L/No 20 
21.     nam1    L/No 21
Then the code retuns this:-
Code:
[COLOR=royalblue][B]Row No [/B][/COLOR][COLOR=royalblue][B]Col(A) [/B][/COLOR][COLOR=royalblue][B]Col(B) [/B][/COLOR][COLOR=royalblue][B]Col(C)  [/B][/COLOR] [COLOR=royalblue][B]Col(D)  [/B][/COLOR] [COLOR=royalblue][B]Col(E)  [/B][/COLOR] [COLOR=royalblue][B]Col(F)  [/B][/COLOR]
1.      nam1    L/No 1  L/No 6   L/No 11  L/No 16  L/No 21 
2.      nam2    L/No 2  L/No 7   L/No 12  L/No 17          
3.      nam3    L/No 3  L/No 8   L/No 13  L/No 18          
4.      nam4    L/No 4  L/No 9   L/No 14  L/No 19          
5.      nam5    L/No 5  L/No 10  L/No 15  L/No 20
If you getting an error on that line, hold the cursor over "Rng.count" and "Column,Count" and tell me what it says, I can't get the code to reproduce you error unless I remove some of the previou line. Have you got all the code ???.
Mick
 
Upvote 0
Hi:

Yes the description you gave is correct I have multiple rows showing up for 1 person.

Here is what I am doing:

1. have my spreadsheet open that has the data
2. Alt F11 to get the VB page > insert > new module then pasted your code
3. now am getting the following error:
ray(Q(3), Q(0)) = Dn.Offset(, 1)

Can you send me step by step instructions? I have not used VB in a long time.
 
Upvote 0
Okay here is the issue,

The license code is in column M and the description is in column N.

I origionally gave column J. Also the employee does not have the same # of licenses.
 
Upvote 0
I changed the "J" to "N": the error I am getting is on the bold underlined line below. When I hover over it it says Ray(Q(3),Q(0))=<Subscript out of range>=Q

What does this mean?
**************************************
Sub MG02Mar43()
Dim Rng As Range, Dn As Range, n As Long
Dim Q, oMax As Integer
Set Rng = Range(Range("N1"), Range("N" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To Columns.Count)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
n = n + 1
ray(n, 1) = Dn.Value: ray(n, 2) = Dn.Offset(, 1)
.Add Dn.Value, Array(2, ray(n, 1), ray(n, 2), n)
Else
Q = .Item(Dn.Value)
Q(0) = Q(0) + 1
ray(Q(3), Q(0)) = Dn.Offset(, 1)
.Item(Dn.Value) = Q
oMax = Application.Max(oMax, Q(0))
End If
Next
Sheets("Sheet2").Range("A1").Resize(.Count, oMax) = ray
End With
End Sub
 
Upvote 0
If your previous data was in columns "J & K " and is now "M & N" then you should change the "Rng" variable to:-

Code:
Set Rng = Range(Range("M1"), Range("M" & Rows.Count).End(xlUp))
Mick
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,854
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top