Combine Rows With the Same Unique ID but Keep Them Separated by Month

[goob90]

I have employee data broken down by employee name, id, deduction month, deduction code, and deduction amount. I want to use VBA to combine the rows with the same employee id, list the deduction codes, and total the deduction amount. I want to do this but leave it separated by deduction month. Could someone point me in the right direction to do this?

 

[alansidman]

Use Power Pivot to achieve this. Look at this link on how to do it.

IMA Site Maintenance

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I want to use VBA to combine the rows with the same employee id, list the deduction codes, and total the deduction amount.

I didn't understand why in your sample Janine only has "MPHO" in the 4th result column but see if this does what you want.

Sub Rearrange()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String
  
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("E" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = Join(Application.Index(a, i, Array(1, 2, 3)), ";") & ";"
    If Not d.exists(s) Then d(s) = s & ";0"
    d(s) = s & Split(d(s), ";")(3) & "," & a(i, 4) & ";" & (Split(d(s), ";")(4) + a(i, 5))
  Next i
  Application.ScreenUpdating = False
  With Range("G2").Resize(d.Count)
    .Value = Application.Transpose(d.items)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False
    .Offset(, 3).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
    .CurrentRegion.Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

Here is my sample data and the result of the above code.

goob90.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	a	1	5	X	1		a	1	5	X,Q,X	6
3	a	1	5	Q	2		a	1	4	X,Q,X	15
4	a	1	5	X	3		b	2	5	Y,Q,Y	24
5	a	1	4	X	4		b	2	4	Y,Q,Y	33
6	a	1	4	Q	5
7	a	1	4	X	6
8	b	2	5	Y	7
9	b	2	5	Q	8
10	b	2	5	Y	9
11	b	2	4	Y	10
12	b	2	4	Q	11
13	b	2	4	Y	12
14
Sheet1

 

[goob90]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.


I didn't understand why in your sample Janine only has "MPHO" in the 4th result column but see if this does what you want.

Sub Rearrange()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  Dim s As String
 
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("A2", Range("E" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = Join(Application.Index(a, i, Array(1, 2, 3)), ";") & ";"
    If Not d.exists(s) Then d(s) = s & ";0"
    d(s) = s & Split(d(s), ";")(3) & "," & a(i, 4) & ";" & (Split(d(s), ";")(4) + a(i, 5))
  Next i
  Application.ScreenUpdating = False
  With Range("G2").Resize(d.Count)
    .Value = Application.Transpose(d.items)
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False
    .Offset(, 3).TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(1, 1))
    .CurrentRegion.Columns.AutoFit
  End With
  Application.ScreenUpdating = True
End Sub

Here is my sample data and the result of the above code.

goob90.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	a	1	5	X	1		a	1	5	X,Q,X	6
3	a	1	5	Q	2		a	1	4	X,Q,X	15
4	a	1	5	X	3		b	2	5	Y,Q,Y	24
5	a	1	4	X	4		b	2	4	Y,Q,Y	33
6	a	1	4	Q	5
7	a	1	4	X	6
8	b	2	5	Y	7
9	b	2	5	Q	8
10	b	2	5	Y	9
11	b	2	4	Y	10
12	b	2	4	Q	11
13	b	2	4	Y	12
14
Sheet1

Thank you so much! That worked perfectly!

The reason Janine only had MPHO in the 4th column is that I forgot to add the MPB and other MPHO to it. Also, I will check out XL2BB,

Again, thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[goob90]

You're welcome. Thanks for the follow-up.

The code is still working great! I have run into an issue with it when running with large amounts of data. I am running it with around 26,000 rows and it is freezing up and ultimately Excel shows "Not Responding". Do you have any advice?

 

[alansidman]

Have you investigated the Power Pivot solution I suggested?

 

[goob90]

Have you investigated the Power Pivot solution I suggested?

Yes! Thank you for following up! I've actually used Power Pivot for a report like this several times. I am only using a MACRO because I had a button connected to it and it makes it easy for some of my coworkers who are not computer savvy able to run the report a little easier.

 

[Peter_SSs]

Just wondering, in the desired format, do you want/need repeated items in the deduction code column like below or would "MPLO" listed just once be just as good?

 

[goob90]

Just wondering, in the desired format, do you want/need repeated items in the deduction code column like below or would "MPLO" listed just once be just as good?

View attachment 71494

I think just once would be okay.