aydinrotar
New Member
- Joined
- Jul 11, 2017
- Messages
- 10
Hi,
I'm trying to automate a really long task I have to preform daily at work with VBA and although I'm almost a complete noob with VBA, I have managed to get majority of it done except for this one last step, so any help would be greatly appreciated.
Basically I have a worksheet with 4 columns of data, I'm looking for some code to check the values in column A and if there's a duplicate, merge the duplicates and sum the values only in column D.
Example:
I need something like this..
<tbody>
</tbody>
To turn into this..
<tbody>
</tbody>
Note: Columns A,B and C will always have the same value if they're a duplicate and only column D needs to be added.
I've found this online but cannot for the life of me work out how to customize it to suite my needs:
Again, any help would really be appreciated
I'm trying to automate a really long task I have to preform daily at work with VBA and although I'm almost a complete noob with VBA, I have managed to get majority of it done except for this one last step, so any help would be greatly appreciated.
Basically I have a worksheet with 4 columns of data, I'm looking for some code to check the values in column A and if there's a duplicate, merge the duplicates and sum the values only in column D.
Example:
I need something like this..
NUMBER | NAME | CODE | HOURS |
1 | John Smith | 410 | 8 |
1 | John Smith | 410 | 6 |
1 | John Smith | 410 | 8 |
2 | Jane Smith | 410 | 7 |
2 | Jane Smith | 410 | 7 |
2 | Jane Smith | 410 | 6.5 |
2 | Jane Smith | 410 | 6.5 |
<tbody>
</tbody>
To turn into this..
NUMBER | NAME | CODE | HOURS |
1 | John Smith | 410 | 22 |
2 | Jane Smith | 410 | 27 |
<tbody>
</tbody>
Note: Columns A,B and C will always have the same value if they're a duplicate and only column D needs to be added.
I've found this online but cannot for the life of me work out how to customize it to suite my needs:
Code:
[COLOR=blue]Sub[/COLOR] test()
[COLOR=blue]Dim[/COLOR] a, i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], ii [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], n [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
a = Sheets("STO REV").Cells(1).CurrentRegion.Value
n = 1
[COLOR=blue]With[/COLOR] CreateObject("Scripting.Dictionary")
[COLOR=blue]For[/COLOR] i = 2 [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](a, 1)
[COLOR=blue]If[/COLOR] [COLOR=blue]Not[/COLOR] .exists(a(i, 2)) [COLOR=blue]Then[/COLOR]
n = n + 1: .Item(a(i, 2)) = n
[COLOR=blue]For[/COLOR] ii = 1 [COLOR=blue]To[/COLOR] [COLOR=blue]UBound[/COLOR](a, 2)
a(n, ii) = a(i, ii)
[COLOR=blue]Next[/COLOR]
[COLOR=blue]Else[/COLOR]
a(.Item(a(i, 2)), 5) = a(.Item(a(i, 2)), 5) + a(i, 5)
[COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]Next[/COLOR]
[COLOR=blue]End With[/COLOR]
[COLOR=blue]With[/COLOR] Sheets.Add().Cells(1).Resize(n, [COLOR=blue]UBound[/COLOR](a, 2))
.Value = a
.Columns.AutoFit
[COLOR=blue]End With[/COLOR]
[COLOR=blue]End Sub[/COLOR][COLOR=blue]
[/COLOR]
Again, any help would really be appreciated