sumif with text and number and special symbol

[Vishaal]

we want to do following

first we want to use unique value from sheet1!B1:B to Sheet2!B1

after that sum if condition match in Sheet2!C2
after that sum if condition if match in Sheet2!D2

query sheet

Book1
	B	C	D
1	Code	No	Amount
2	847-1998	1	450
3	sde-feb-2023	4	950
4	sde/SEP/1998	25	5674
5	dfe-April-24	65	87
6	852-1998	6	98
7	8754	39	9310
8	9874	60	8400
9	909-1981	67	987
10	736	21	2900
11	dde-NOV-1075	67	98
12	ddf-DEC-1975	67	999
13	847-1998	5	540
14	sde-feb-2023	4	950
15	909-1981	3	3
Sheet1

Result Sheet

Book1
	B	C	D
1	Code	No	Amount
2	847-1998	6	990
3	sde-feb-2023	8	1900
4	sde/SEP/1998	25	5674
5	dfe-April-24	65	87
6	852-1998	6	98
7	8754	39	9310
8	9874	60	8400
9	909-1981	70	990
10	736	21	2900
11	dde-NOV-1075	67	98
12	ddf-DEC-1975	67	999
Sheet2

 

[Kerryx]

Power query

Book3
	A	B	C
2	847-1998	6	990
3	sde-feb-2023	8	1900
4	sde/SEP/1998	25	5674
5	dfe-April-24	65	87
6	852-1998	6	98
7	8754	39	9310
8	9874	60	8400
9	909-1981	70	990
10	736	21	2900
11	dde-NOV-1075	67	98
12	ddf-DEC-1975	67	999
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type any}, {"No", Int64.Type}, {"Amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"No", each List.Sum([No]), type nullable number}, {"Amount", each List.Sum([Amount]), type nullable number}})
in
    #"Grouped Rows"

 

[Vishaal]

Thanks Kerryx

Can we do it through VBA

I will also check today, how to use power query

 

[Kerryx]

More info here

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...

www.mrexcel.com

 

[Alex Blakenburg]

Not sure why you don't just use a pivot table but if you really need to stick with VBA see if this helps.

Sub SummariseData()

    Dim shtSrc As Worksheet, shtOut As Worksheet
    Dim rngSrc As Range, rngOut As Range
    Dim arrSrc As Variant, arrOut As Variant
    Dim i As Long, rowOut As Long
    
    Dim dictData As Object, dictKey As String
    
    Set shtSrc = Worksheets("Sheet1")
    With shtSrc
        Set rngSrc = .Range("A2:C" & .Cells(Rows.Count, "A").End(xlUp).Row)
        arrSrc = rngSrc.Value2
    End With
    
    Set shtOut = Worksheets("Sheet2")
    With shtOut
        Set rngOut = .Range("A1")
        rngOut.CurrentRegion.Offset(1).ClearContents
        rngSrc.Resize(1).Offset(-1).Copy Destination:=rngOut
        ReDim arrOut(1 To UBound(arrSrc), 1 To UBound(arrSrc, 2))
    End With

    Set dictData = CreateObject("Scripting.dictionary")
    
    For i = 1 To UBound(arrSrc)
        dictKey = arrSrc(i, 1)
        If dictData.exists(dictKey) Then
            arrOut(dictData(dictKey), 2) = arrOut(dictData(dictKey), 2) + arrSrc(i, 2)
            arrOut(dictData(dictKey), 3) = arrOut(dictData(dictKey), 3) + arrSrc(i, 3)
        Else
            rowOut = rowOut + 1
            dictData(dictKey) = rowOut
            arrOut(rowOut, 1) = arrSrc(i, 1)
            arrOut(rowOut, 2) = arrSrc(i, 2)
            arrOut(rowOut, 3) = arrSrc(i, 3)
        End If
    Next i
    
    rngOut.Offset(1).Resize(rowOut, UBound(arrOut, 2)).Value2 = arrOut

End Sub

 

[Vishaal]

thanks Alex Blakenburg

 

[Alex Blakenburg]

You're welcome. Glad we could help.