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]

Maybe use a pivot table. Here is the results I got:

      -----L----- --------M-------- ----------N---------- ----------O---------- ------------P------------
  1   Row Labels  Sum of Rel. Funds Sum of ACA Rel. Funds Sum of Utilized Funds Sum of ACA Utilized Funds
  2   Arizona                  7367                  9009                  5511                     16913
  3   California               8687                  6124                 12157                     12145
  4   Florida                  6626                 11978                 11475                     11162
  5   Missouri                 6282                 18787                  5044                     13782
  6   Ohio                     4853                  8213                  4955                       205
  7   Texas                   11405                 56067                 11908                     12908
  8   Wyoming                   140                  5353                  7209                       961
  9   Grand Total             45360                115531                 58259                     68076

 

[Arjun.Singh]

Thank you Amas. I thought about it but I need vb to get it done

 

[Arjun.Singh]

Help! Ty vm!

 

[Arjun.Singh]

Help please, I'm sleepless just thinking about a solution

Many thanks!

 

[AMAS]

I started working on a solution but it's not working yet. Let me see if I can make it work. Also you could use vba to create a pivot table that will do the same thing, but I haven't done this before. Maybe try recoring a pivot table using the macro recorder and seeing what you get.

AMAS

 

[AMAS]

Hi,

Try this:

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
  ' Define sheets
    Set sh1 = Sheets(1)
    Set sh2 = Sheets(2)
  ' Convert range to first array
    arrData = sh1.Range("A2:H" & 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 = 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 = 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:H8")
        .Header = xlYes
        .Apply
    End With
End Sub

Here is what the result looks like:

      ----A----- -B-- ---C--- -D-- ----E----- ------F------- ------G------- --------H---------
  1   State      Year Quarter Date Rel. Funds ACA Rel. Funds Utilized Funds ACA Utilized Funds
  2   Arizona                            2514           4340            189               9569
  3   California                         7375           1262           3849                751
  4   Florida                            5110           5783           1960               2182
  5   Missouri                           1172           9860           3014               9772
  6   Ohio                               4853           8213           4955                205
  7   Texas                              1516          45360           2552               3525
  8   Wyoming                             140           5353           7209                961

 

[Arjun.Singh]

Thank you Amas, I'll give this a try in a bit and come back. Thank you for taking the time

 

[Arjun.Singh]

Amas! It works like magic!! Didnt require a single bit or byte to be changed! Perfect!! Thank you again!!

 

[AMAS]

Glad it worked out for you. Thanks for the feedback.

Good luck.

AMAS