Aggregating data

[tiger_78]

Sample of Contract information - "Aggregate".

1. "List Info I have (4 Columns)"
2. "Aggregated Info I want (4 Columns)"

Each CONTRACT_NO is the main field here, some CONTRACT_NO's have either 1 or 2 Rows of Contract data, that I'd like to aggregate/summarise.

Basically in Excel (not using Power Query), I want to use the 10 rows of data (from Tab 1), so that it is aggregated down to the 6 rows of data (in Tab 2).
Thanks.

1. "List Info I have (4 Columns)"

PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM
10P3A	15196000	2432.00	2372.68
10P3A	15196000	3648.00	3648.00
10P3A	15205210	1632.00	1592.50
10P3A	15205210	2448.00	2479.50
REB5S	0276537657657	1152.35	1095.55
45P3A	16729380	2549.50	2162.17
45P3A	16729380	3488.00	3425.50
REW5B	08765421087657	1844.50	1834.50
REW5B	08765421087657	2108.00	2009.35
WEV1P	01113812003792	892.40	881.72

2. "Aggregated Info I want (4 Columns)"

PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM
10P3A	15196000	6080.68	5527.22
10P3A	15205210	4080.57	4072.00
REB5S	0276537657657	1152.35	1095.55
45P3A	16729380	6037.50	5587.67
REW5B	08765421087657	3952.50	3843.85
WEV1P	01113812003792	892.40	881.72

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM
2	10P3A	15196000	2432.00	2372.68			10P3A	15196000	6080.00	6020.68
3	10P3A	15196000	3648.00	3648.00			10P3A	15205210	4080.00	4072.00
4	10P3A	15205210	1632.00	1592.50			REB5S	276537657657	1152.35	1095.55
5	10P3A	15205210	2448.00	2479.50			45P3A	16729380	6037.50	5587.67
6	REB5S	276537657657	1152.35	1095.55			REW5B	8765421087657	3952.50	3843.85
7	45P3A	16729380	2549.50	2162.17			WEV1P	1113812003792	892.40	881.72
8	45P3A	16729380	3488.00	3425.50
9	REW5B	8765421087657	1844.50	1834.50
10	REW5B	8765421087657	2108.00	2009.35
11	WEV1P	1113812003792	892.40	881.72
12
Data

Cell Formulas
Range	Formula
G2:H7	G2	=UNIQUE(A2:B11)
I2:J7	I2	=SUMIF($B:$B,$H2,C:C)
Dynamic array formulas.

 

[KevCarter]

Book1
	A	B	C	D
1	PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM
2	10P3A	15196000	2,432.00	2,372.68
3	10P3A	15196000	3,648.00	3,648.00
4	10P3A	15205210	1,632.00	1,592.50
5	10P3A	15205210	2,448.00	2,479.50
6	REB5S	276537657657	1,152.35	1,095.55
7	45P3A	16729380	2,549.50	2,162.17
8	45P3A	16729380	3,488.00	3,425.50
9	REW5B	8765421087657	1,844.50	1,834.50
10	REW5B	8765421087657	2,108.00	2,009.35
11	WEV1P	1113812003792	892.40	881.72
12
13	10P3A	15196000	6,080.00	6,020.68
14	10P3A	15205210	4,080.00	4,072.00
15	REB5S	276537657657	1,152.35	1,095.55
16	45P3A	16729380	6,037.50	5,587.67
17	REW5B	8765421087657	3,952.50	3,843.85
18	WEV1P	1113812003792	892.40	881.72
Sheet1

Cell Formulas
Range	Formula
A13:B18	A13	=UNIQUE(A2:B11)
C13:C18	C13	=SUMIFS(C2:C11,A2:A11,A13:A18,B2:B11,B13:B18)
D13:D18	D13	=SUMIFS(D2:D11,A2:A11,A13:A18,B2:B11,B13:B18)
Dynamic array formulas.

LOL Fluff is too fast. It threw me that the OP had an incorrect number in his aggregate net...

 

[tiger_78]

When I type the first formula =UNIQUE(A2:B11) into cell A13, I just get:
#NAME?

 

[mumps]

Make sure that Sheet3 has headers and that Sheet1 is the active sheet before trying this macro:

Sub aggregateData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, num As String, arr As Variant, dic As Object, i As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    arr = srcWS.Range("A2:A" & LastRow).Resize(, 4).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr)
        num = arr(i, 2)
        If Not dic.Exists(num) Then
            If WorksheetFunction.CountIf(Range("B:B"), num) > 1 Then
                dic.Add num, Nothing
                With desWS
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(arr(i, 1), Trim(arr(i, 2)))
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(, 2).Value = Array(WorksheetFunction.Sum(Trim(arr(i, 3)), Trim(arr(i + 1, 3))), WorksheetFunction.Sum(Trim(arr(i, 4)), Trim(arr(i + 1, 4))))
                End With
            Else
                With desWS
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(arr(i, 1), Trim(arr(i, 2)))
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Resize(, 2).Value = Array(Trim(arr(i, 3)), Trim(arr(i, 4)))
                End With
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[tiger_78]

Hi mumps. thanks though I don't wish to use a Macro, just the simple Excel.
I want to keep the query simple.

 

[Fluff]

When I type the first formula =UNIQUE(A2:B11) into cell A13, I just get:
#NAME?

Did you try that with 365?

 

[tiger_78]

Hi I am using Excel 2013 for this

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM			PROD_CODE	CONTRACT_NO	GROSS PREMIUM	NET PREMIUM
2	10P3A	15196000	2432.00	2372.68			10P3A	15196000	6080.00	6020.68
3	10P3A	15196000	3648.00	3648.00			10P3A	15205210	4080.00	4072.00
4	10P3A	15205210	1632.00	1592.50			REB5S	276537657657	1152.35	1095.55
5	10P3A	15205210	2448.00	2479.50			45P3A	16729380	6037.50	5587.67
6	REB5S	276537657657	1152.35	1095.55			REW5B	8765421087657	3952.50	3843.85
7	45P3A	16729380	2549.50	2162.17			WEV1P	1113812003792	892.40	881.72
8	45P3A	16729380	3488.00	3425.50
9	REW5B	8765421087657	1844.50	1834.50
10	REW5B	8765421087657	2108.00	2009.35
11	WEV1P	1113812003792	892.40	881.72
12
Data

Cell Formulas
Range	Formula
G2:G11	G2	=IFNA(INDEX($A$2:$A$11,MATCH(H2,$B$2:$B$11,0)),"")
H2:H11	H2	=IFERROR(INDEX($B$2:$B$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$11,H$1:H1,0))),1)),"")
I2:J7	I2	=SUMIF($B:$B,$H2,C:C)

 

[Fluff]

Cross posted Aggregating data

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.