Looking for duplicates and merging data

[FatalLordes]

Hi all again

So I have a new question. As always, I will try and solve it myself but I always like to post here as, even if I do solve it, normally I'm shown a much better way here which I can then learn from

My issue is now that I have duplicate rows which I need to remove BUT I need to merge the data. Below is an example of some data:

It needs to look at Column A and if duplicate columns with that same number appears, it needs to merge the data, specifically Column C and G as that is the only two columns that should have different data. I hope my example is clear enough to show what it starts with and what it needs to become. And I need to do this as part of a VBA macro as I'm doing other stuff in VBA to this report as well but I'm stuck on the duplicate row issue.

Any help to point me in the right direction would be greatly appreciated.

Thanks in advance.

 

[bebo021999]

Try below code:

Option Explicit
Sub combi()
Dim i&, k&, rng, res(), id As String, s1 As String, s2 As String
Dim dic As Object, key, sp
Set dic = CreateObject("Scripting.Dictionary")
rng = Range("A2").CurrentRegion.Value
For i = 1 To UBound(rng)
    id = rng(i, 1) & "|" & rng(i, 2) & "|" & rng(i, 4) & "|" & rng(i, 5) & "|" & rng(i, 6)
    If Not dic.exists(id) Then
        dic.Add id, rng(i, 3) & "|" & rng(i, 7)
    Else
        s1 = Split(dic(id), "|")(0) & IIf(rng(i, 3) = "", "", "," & rng(i, 3))
        s2 = Split(dic(id), "|")(1) & IIf(rng(i, 7) = "", "", "," & rng(i, 7))
        dic(id) = s1 & "|" & s2
    End If
Next
ReDim res(1 To dic.Count, 1 To UBound(rng, 2))
For Each key In dic.keys
    k = k + 1: sp = Split(key, "|")
    res(k, 1) = sp(0): res(k, 2) = sp(1): res(k, 4) = sp(2)
    res(k, 5) = sp(3): res(k, 6) = sp(4)
    sp = Split(dic(key), "|")
    res(k, 3) = IIf(Left(sp(0), 1) = ",", Mid(sp(0), 2, 255), sp(0))
    res(k, 7) = IIf(Left(sp(1), 1) = ",", Mid(sp(1), 2, 255), sp(1))
Next
Range("A10").Resize(UBound(res), UBound(res, 2)).Value = res
End Sub

Test.xlsm
	A	B	C	D	E	F	G
1
2	913753	PEXIP	2	TEL	29/09/2023 13:00	29/09/2023 13:15	DEF
3	913753	PEXIP	28	TEL	29/09/2023 13:00	29/09/2023 13:15	None
4	913753	PEXIP		TEL	29/09/2023 13:00	29/09/2023 13:15
5	913753	PEXIP	4	TEL	29/09/2023 13:00	29/09/2023 13:15
6	912960	ICJ		Ext	29/09/2023 18:00	29/09/2023 18:15
7	912960	ICJ	28	Ext	29/09/2023 18:00	29/09/2023 18:15	ABC
8
9
10	913753	PEXIP	2,28,4	TEL	29/09/2023 13:00:00	29/09/2023 13:15:00	DEF,None
11	912960	ICJ	28	Ext	29/09/2023 18:00:00	29/09/2023 18:15:00	ABC
Sheet1

 

[FatalLordes]

Wow, that was fast! Thank you, I will try this now!

 

[FatalLordes]

Wow, that was fast! Thank you, I will try this now!

So, I'm thinking I need to remove some lines because I only want the end result to be what is shown in rows 10 and 11 in my sample, which would end up being rows 1 and 2 as I don't need to see any duplicates. But I'm not sure. The sample I gave was just a small sample. The original report may be hundreds if not thousands of rows....