Combine records & add values

[Arjun.Singh]

Hello,

Finding it a bit tough to crack, I have the following records (multiple) which need to be combined so that each state has only one record (making it a unique record) with the corresponding fund values added..

Excel Workbook
	A	B	C	D	E	F	G	H
1	State	Year	Quarter	Date	Rel. Funds	ACA Rel. Funds	Utilized Funds	ACA Utilized Funds
2	Texas	2008	Q3	22-Sep-08	1516	45360	2552	3525
3	Arizona	2008	Q1	10-Mar-08	2514	4340	189	9569
4	Missouri	2010	Q3	8-Aug-10	1172	9860	3014	9772
5	Florida	2010	Q3	28-Oct-10	5110	5783	1960	2182
6	California	2010	Q3	11-Sep-10	7375	1262	3849	751
7	Ohio	2011	Q2	21-Apr-11	4853	8213	4955	205
8	Wyoming	2012	Q1	30-Jan-12	140	5353	7209	961
9	Florida	2012	Q3	17-Oct-12	1516	6195	9515	8980
10	Texas	2012	Q3	31-Oct-12	2514	3908	6541	3380
11	California	2014	Q2	15-Jul-14	1172	1318	1120	5483
12	Missouri	2014	Q2	24-Jul-14	5110	8927	2030	4010
13	Texas	2014	Q2	8-Jul-14	7375	6799	2815	6003
14	Arizona	2014	Q3	19-Aug-14	4853	4669	5322	7344
15	California	2014	Q3	17-Nov-14	140	3544	7188	5911
Sheet1

Desired outcome is (the sum of all the funds, year info remove as it is assumed that the user only need totals) :

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:149.6px;"><col style="width:80px;"><col style="width:64px;"><col style="width:148px;"><col style="width:116.8px;"><col style="width:115.2px;"><col style="width:126.4px;"><col style="width:145.6px;"></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:42px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; ">State</td><td style="color:#ffffff; background-color:#333399; font-weight:bold; font-family:Verdana; text-align:center; ">Year</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; ">Quarter</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; ">Date</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; "> Rel. Funds</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; ">ACA Rel. Funds</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; "> Utilized Funds</td><td style="background-color:#333399; color:#ffffff; font-weight:bold; font-family:Verdana; text-align:center; ">ACA Utilized Funds</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="font-family:Verdana; text-align:left; ">Texas</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; text-align:right; ">8-Jul-14</td><td style="font-family:Verdana; text-align:right; ">11405</td><td style="font-family:Verdana; text-align:right; ">56067</td><td style="font-family:Verdana; text-align:right; ">11908</td><td style="font-family:Verdana; text-align:right; ">12908</td></tr><tr style="height:22px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="font-family:Verdana; text-align:left; ">California</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; ">
</td><td style="font-family:Verdana; text-align:right; ">17-Nov-14</td><td style="font-family:Verdana; text-align:right; ">8687</td><td style="font-family:Verdana; text-align:right; ">6124</td><td style="font-family:Verdana; text-align:right; ">12157</td><td style="font-family:Verdana; text-align:right; ">12145</td></tr></table>

The elaborate data will be replaced by the data shown above on the same sheet.

Many thanks in advance!

 

[AMAS]

Hi,

Here's an updated version that will retain the most recent year (if of interest to you):

Option Explicit
Option Base 1

Sub CalcStates()

Dim arrData, arrResults
Dim x As Integer, y As Long, z As Integer, c As Integer, FoundIt As Boolean
Dim sh1 As Worksheet, sh2 As Worksheet

'On Error Resume Next

  ' Define sheets
    Set sh1 = Sheets(1)
    Set sh2 = Sheets(2)

  ' Convert range to first array
    arrData = sh1.Range("A2:H" & sh1.Range("A" & Rows.Count).End(xlUp).Row)

  ' Provide dimensions for second array
    ReDim arrResults(8, 1)

  ' Make sure Boolean is set correctly
    FoundIt = False

  ' Compare the two arrays and sum the results
    For x = LBound(arrData, 1) To UBound(arrData, 1)
        For y = LBound(arrResults, 2) To UBound(arrResults, 2)
            If arrData(x, 1) = arrResults(1, y) Then
                For c = 2 To 4
                    If c <> 3 Then arrResults(c, y) = Application.Max(arrResults(c, y), arrData(x, c))
                Next
                For c = 5 To 8
                    arrResults(c, y) = arrResults(c, y) + arrData(x, c)
                Next
                FoundIt = True ' found the correct state
                Exit For
            End If
        Next
      ' If the state could not be found in the second array
        If FoundIt = False Then
            z = z + 1
            ReDim Preserve arrResults(8, z)
            arrResults(1, z) = arrData(x, 1)
                For c = 2 To 4
                    If c <> 3 Then arrResults(c, z) = Application.Max(arrResults(c, z), arrData(x, c))
                Next
            For c = 5 To 8
                arrResults(c, z) = arrResults(c, z) + arrData(x, c)
            Next
        End If
    Next

  ' Print results to second sheet in workbook
    With sh2
        .Range("A1:H1") = Array("State", "Year", "Quarter", "Date", "Rel. Funds", "ACA Rel. Funds", "Utilized Funds", "ACA Utilized Funds")
        .Range("A2:H" & UBound(arrResults, 2) + 1) = Application.Transpose(arrResults)
        .Columns("A:H").AutoFit
    End With

  ' Add sort options to sort by State
    With sh2.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A2:A8")
        .SetRange Range("A1:H" & sh2.Range("A" & Rows.Count).End(xlUp).Row)
        .Header = xlYes
        .Apply
    End With

End Sub