Filter Unique records with Adding and Concatenate from duplicate Rows

[NiladriACN]

Hi All,

This is my first post here and hope will get my answer here. I have a table with 3 columns, store, Weighting and matrix. I need the unique store list from the table but weighting and matrix columns should be added or concatenated before deleting the duplicate rows. Please find the below input and output table. Stored no is sorted as an order

Input Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Weighting[/TD]
[TD]Matrix[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]10[/TD]
[TD]ABC>10[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]15[/TD]
[TD]DEF = 15[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]27[/TD]
[TD]CDE <= 22[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]12[/TD]
[TD]ABC = 2[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]10[/TD]
[TD]DEF = 25[/TD]
[/TR]
</tbody>[/TABLE]

Output Table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]weighting[/TD]
[TD]Matrix[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]52 (10+15+27)[/TD]
[TD]ABC > 10;DEF =15 ;CDE <=22[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]22 (12 +10)[/TD]
[TD]ABC = 2 ; DEF = 25[/TD]
[/TR]
</tbody>[/TABLE]

Is there any formula or code to solve this issue ? I have more than 10K rows so manually checking it's time consuming.

Please help!!

Regards,
Niladri

****** id="cke_pastebin" style="position: absolute; top: 322.667px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]ABC>10[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[sandy666]

you can try PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Store[/td][td=bgcolor:#5B9BD5]Weighting[/td][td=bgcolor:#5B9BD5]Matrix[/td][td][/td][td=bgcolor:#70AD47]Store[/td][td=bgcolor:#70AD47]Weighting[/td][td=bgcolor:#70AD47]Matrix[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]ABC>10[/td][td][/td][td=bgcolor:#E2EFDA]

30​

[/td][td=bgcolor:#E2EFDA]

52​

[/td][td=bgcolor:#E2EFDA]ABC>10;DEF = 15;CDE <= 22[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30​

[/td][td]

15​

[/td][td]DEF = 15[/td][td][/td][td]

20​

[/td][td]

22​

[/td][td]ABC = 2;DEF = 25[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

27​

[/td][td=bgcolor:#DDEBF7]CDE <= 22[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

20​

[/td][td]

12​

[/td][td]ABC = 2[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

20​

[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]DEF = 25[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Store"}, {{"Weighting", each List.Sum([Weighting]), type number}, {"Count", each _, type table}}),
    ToList = Table.AddColumn(Group, "Matrix", each Table.Column([Count],"Matrix")),
    Extract = Table.TransformColumns(ToList, {"Matrix", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
    Extract[/SIZE]

 

[Marcelo Branco]

Maybe this macro...

Sub aTest()
    Dim dic As Object, vData As Variant, i As Long
    Dim vAux As Variant, vKey As Variant, vResult As Variant
    
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    'Assumes data in columns A:C, headers in row 1
    vData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    For i = LBound(vData, 1) To UBound(vData, 1)
        If dic.exists(vData(i, 1)) Then
            vAux = dic(vData(i, 1))
            vAux(0) = vAux(0) + vData(i, 2)
            vAux(1) = vAux(1) & ";" & vData(i, 3)
            dic(vData(i, 1)) = vAux
        Else
            dic(vData(i, 1)) = Array(vData(i, 2), vData(i, 3))
        End If
    Next i
    
    'Output in columns E:G
    Range("E1:G1") = Array("Store", "Weighting", "Matrix")
    vResult = Range("E2").Resize(dic.Count, 3)
    i = 0
    For Each vKey In dic.keys
        i = i + 1
        vResult(i, 1) = vKey
        vResult(i, 2) = dic(vKey)(0)
        vResult(i, 3) = dic(vKey)(1)
    Next vKey
    Range("E2").Resize(dic.Count, 3) = vResult
    Columns("E:G").AutoFit
End Sub

M.