Aggregating data

tiger_78

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
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_CODECONTRACT_NOGROSS PREMIUMNET PREMIUM
10P3A151960002432.002372.68
10P3A151960003648.003648.00
10P3A152052101632.001592.50
10P3A152052102448.002479.50
REB5S02765376576571152.351095.55
45P3A167293802549.502162.17
45P3A167293803488.003425.50
REW5B087654210876571844.501834.50
REW5B087654210876572108.002009.35
WEV1P01113812003792892.40881.72

2. "Aggregated Info I want (4 Columns)"
PROD_CODECONTRACT_NOGROSS PREMIUMNET PREMIUM
10P3A151960006080.685527.22
10P3A152052104080.574072.00
REB5S02765376576571152.351095.55
45P3A167293806037.505587.67
REW5B087654210876573952.503843.85
WEV1P01113812003792892.40881.72
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,322
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJ
1PROD_CODECONTRACT_NOGROSS PREMIUMNET PREMIUM
210P3A151960002432.002372.6810P3A151960006080.006020.68
310P3A151960003648.003648.0010P3A152052104080.004072.00
410P3A152052101632.001592.50REB5S2765376576571152.351095.55
510P3A152052102448.002479.5045P3A167293806037.505587.67
6REB5S2765376576571152.351095.55REW5B87654210876573952.503843.85
745P3A167293802549.502162.17WEV1P1113812003792892.40881.72
845P3A167293803488.003425.50
9REW5B87654210876571844.501834.50
10REW5B87654210876572108.002009.35
11WEV1P1113812003792892.40881.72
12
Data
Cell Formulas
RangeFormula
G2:H7G2=UNIQUE(A2:B11)
I2:J7I2=SUMIF($B:$B,$H2,C:C)
Dynamic array formulas.
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
145
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Book1
ABCD
1PROD_CODECONTRACT_NOGROSS PREMIUMNET PREMIUM
210P3A151960002,432.002,372.68
310P3A151960003,648.003,648.00
410P3A152052101,632.001,592.50
510P3A152052102,448.002,479.50
6REB5S2765376576571,152.351,095.55
745P3A167293802,549.502,162.17
845P3A167293803,488.003,425.50
9REW5B87654210876571,844.501,834.50
10REW5B87654210876572,108.002,009.35
11WEV1P1113812003792892.40881.72
12
1310P3A151960006,080.006,020.68
1410P3A152052104,080.004,072.00
15REB5S2765376576571,152.351,095.55
1645P3A167293806,037.505,587.67
17REW5B87654210876573,952.503,843.85
18WEV1P1113812003792892.40881.72
Sheet1
Cell Formulas
RangeFormula
A13:B18A13=UNIQUE(A2:B11)
C13:C18C13=SUMIFS(C2:C11,A2:A11,A13:A18,B2:B11,B13:B18)
D13:D18D13=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

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
When I type the first formula =UNIQUE(A2:B11) into cell A13, I just get:
#NAME?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,172

ADVERTISEMENT

Make sure that Sheet3 has headers and that Sheet1 is the active sheet before trying this macro:
VBA Code:
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

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
Hi mumps. thanks though I don't wish to use a Macro, just the simple Excel.
I want to keep the query simple.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,322
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When I type the first formula =UNIQUE(A2:B11) into cell A13, I just get:
#NAME?
Did you try that with 365?
 

tiger_78

New Member
Joined
Oct 16, 2020
Messages
14
Office Version
  1. 365
  2. 2013
  3. 2007
Platform
  1. Windows
Hi I am using Excel 2013 for this
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,322
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
+Fluff v2.xlsm
ABCDEFGHIJ
1PROD_CODECONTRACT_NOGROSS PREMIUMNET PREMIUMPROD_CODECONTRACT_NOGROSS PREMIUMNET PREMIUM
210P3A151960002432.002372.6810P3A151960006080.006020.68
310P3A151960003648.003648.0010P3A152052104080.004072.00
410P3A152052101632.001592.50REB5S2765376576571152.351095.55
510P3A152052102448.002479.5045P3A167293806037.505587.67
6REB5S2765376576571152.351095.55REW5B87654210876573952.503843.85
745P3A167293802549.502162.17WEV1P1113812003792892.40881.72
845P3A167293803488.003425.50  
9REW5B87654210876571844.501834.50  
10REW5B87654210876572108.002009.35  
11WEV1P1113812003792892.40881.72  
12
Data
Cell Formulas
RangeFormula
G2:G11G2=IFNA(INDEX($A$2:$A$11,MATCH(H2,$B$2:$B$11,0)),"")
H2:H11H2=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:J7I2=SUMIF($B:$B,$H2,C:C)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,322
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,423
Messages
5,601,574
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top