Append raw data into another sheet

[excel90210]

Hello,

Firstly apologies if this has been asked before - I have tried search but haven't quite found what I am looking for.

I want to be able to copy data from one worksheet (Raw) to another (Accounts). Both Raw and Accounts already have data and I would like the copy to be additive - i.e. pick up only the latest values in Raw and add it to the end of Accounts (before the total row).

Here is the setup:

Raw:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Merchant[/TD]
[TD]Comment[/TD]
[TD]Amount[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]Kickoff meeting[/TD]
[TD]43.40[/TD]
[TD]Meals[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]190Mi @£0.45/Mi[/TD]
[TD]Head office trip[/TD]
[TD]85.50[/TD]
[TD]Fuel/Mileage[/TD]
[/TR]
[TR]
[TD]12/1/2017[/TD]
[TD]10.6Mi "£0.45/Mi[/TD]
[TD]Local meeting[/TD]
[TD]4.77[/TD]
[TD]Fuel/Mileage[/TD]
[/TR]
[TR]
[TD]13/1/2017[/TD]
[TD]Dominos[/TD]
[TD]Local meeting[/TD]
[TD]14.20[/TD]
[TD]Meals[/TD]
[/TR]
[TR]
[TD]14/1/2017[/TD]
[TD]Subs[/TD]
[TD]Subscriptions[/TD]
[TD]98.39[/TD]
[TD]Subscription[/TD]
[/TR]
</tbody>[/TABLE]

(n.b. there are further columns to the right of category which do not need to be copied)

On first copy, the following results would be produced in the Accounts sheet:

Accounts:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Merchant[/TD]
[TD]Description[/TD]
[TD]Dir Loan[/TD]
[TD]Meals[/TD]
[TD]Fuel/Mileage[/TD]
[TD]Subscriptions[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]Kickoff meeting[/TD]
[TD]-43.30[/TD]
[TD]43.30[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]190Mi @£0.45/Mi[/TD]
[TD]Head office trip[/TD]
[TD]-85.50[/TD]
[TD][/TD]
[TD]85.50[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12/1/2017[/TD]
[TD]10.6Mi "£0.45/Mi[/TD]
[TD]Local meeting[/TD]
[TD]-4.77[/TD]
[TD][/TD]
[TD]4.77[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13/1/2017[/TD]
[TD]Dominos[/TD]
[TD]Local meeting[/TD]
[TD]-14.20[/TD]
[TD]14.20[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14/1/2017[/TD]
[TD]Subs[/TD]
[TD]Subscriptions[/TD]
[TD]-98.39[/TD]
[TD][/TD]
[TD][/TD]
[TD]98.39[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD]246.16[/TD]
[TD]57.50[/TD]
[TD]90.27[/TD]
[TD]98.39[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

So as you can see, the Raw data A,B,C has been copied. Column D is the negative of the amount in Raw. And then Columns E, F and G are columns per category in the source raw data. At the end is a Check column (H) which sums the values. (N.B. there may be further columns after Check in the Accounts sheet which shouldn't be affected). After the first copy of data, if a further copy requested without any changes to raw data, no further copy is made. If another 2 rows are added to Raw data and the copy is clicked, the additional rows are then added to the Accounts sheet (before the Totals row). If the Raw data has a new Category (e.g. Transportation), a column containing that category in the Accounts tab should be inserted before the Check column and the appropriate value added.

Example of 2 extra rows now added to Raw sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Merchant[/TD]
[TD]Comment[/TD]
[TD]Amount[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]Kickoff meeting[/TD]
[TD]43.40[/TD]
[TD]Meals[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]190Mi @£0.45/Mi[/TD]
[TD]Head office trip[/TD]
[TD]85.50[/TD]
[TD]Fuel/Mileage[/TD]
[/TR]
[TR]
[TD]12/1/2017[/TD]
[TD]10.6Mi "£0.45/Mi[/TD]
[TD]Local meeting[/TD]
[TD]4.77[/TD]
[TD]Fuel/Mileage[/TD]
[/TR]
[TR]
[TD]13/1/2017[/TD]
[TD]Dominos[/TD]
[TD]Local meeting[/TD]
[TD]14.20[/TD]
[TD]Meals[/TD]
[/TR]
[TR]
[TD]14/1/2017[/TD]
[TD]Subs[/TD]
[TD]Subscriptions[/TD]
[TD]98.39[/TD]
[TD]Subscription[/TD]
[/TR]
[TR]
[TD]15/1/2017[/TD]
[TD]Train to London[/TD]
[TD]Transportation[/TD]
[TD]32.30[/TD]
[TD]Transportation[/TD]
[/TR]
[TR]
[TD]16/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]More Pizza[/TD]
[TD]32.20[/TD]
[TD]Meals[/TD]
[/TR]
</tbody>[/TABLE]

Click copy: the following changes in blue in the
Accounts sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Merchant[/TD]
[TD]Description[/TD]
[TD]Dir Loan[/TD]
[TD]Meals[/TD]
[TD]Fuel/Mileage[/TD]
[TD]Subscriptions[/TD]
[TD]Transportation[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]Kickoff meeting[/TD]
[TD]-43.30[/TD]
[TD]43.30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11/1/2017[/TD]
[TD]190Mi @£0.45/Mi[/TD]
[TD]Head office trip[/TD]
[TD]-85.50[/TD]
[TD][/TD]
[TD]85.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]12/1/2017[/TD]
[TD]10.6Mi "£0.45/Mi[/TD]
[TD]Local meeting[/TD]
[TD]-4.77[/TD]
[TD][/TD]
[TD]4.77[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]13/1/2017[/TD]
[TD]Dominos[/TD]
[TD]Local meeting[/TD]
[TD]-14.20[/TD]
[TD]14.20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14/1/2017[/TD]
[TD]Subs[/TD]
[TD]Subscriptions[/TD]
[TD]-98.39[/TD]
[TD][/TD]
[TD][/TD]
[TD]98.39[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15/1/2017[/TD]
[TD]Train to London[/TD]
[TD]Transportation[/TD]
[TD]-32.30[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]32.30[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]16/1/2017[/TD]
[TD]Pizza Express[/TD]
[TD]More Pizza[/TD]
[TD]-32.20[/TD]
[TD]32.20[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD]410.16[/TD]
[TD]89.70[/TD]
[TD]90.27[/TD]
[TD]98.39[/TD]
[TD]32.30[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Is the above possible? Would someone mind showing me how to do this?

Thanks in advance.

 

[MickG]

Try this on your data for results on sheet 2.

[COLOR="Navy"]Sub[/COLOR] MG29Apr37
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Lst = Range("A" & Rows.Count).End(xlUp).Row
Ray = Range("A1").Resize(Lst + 1, 5)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
 .CompareMode = vbTextCompare
   ReDim nRay(1 To UBound(Ray, 1))
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    nRay(n) = Ray(n, 5)
    [COLOR="Navy"]If[/COLOR] Not .exists(Ray(n, 5)) [COLOR="Navy"]Then[/COLOR]
    .Item(Ray(n, 5)) = .Count
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ReDim Preserve Ray(1 To UBound(Ray, 1), 1 To UBound(Ray, 2) + .Count - 1)
[COLOR="Navy"]Dim[/COLOR] t
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys: Ray(1, .Item(K) + 5) = K: [COLOR="Navy"]Next[/COLOR] K
    Ray(1, 4) = "Dir Loan"
    Ray(UBound(Ray, 1), 1) = "Totals"
    Ray(1, UBound(Ray, 2)) = "Check"
        [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1) - 1
            Ray(n, 5) = ""
            [COLOR="Navy"]For[/COLOR] Ac = 5 To UBound(Ray, 2) - 1
                [COLOR="Navy"]If[/COLOR] Ray(1, Ac) = nRay(n) [COLOR="Navy"]Then[/COLOR]
                      Ray(n, Ac) = Ray(n, 4)
                       Ray(n, UBound(Ray, 2)) = -Ray(n, 4) + Ray(n, Ac)
                       Ray(UBound(Ray, 1), Ac) = Ray(UBound(Ray, 1), Ac) + Ray(n, Ac)
                       Ray(UBound(Ray, 1), 4) = Ray(UBound(Ray, 1), 4) + Ray(n, 4)
                       Ray(UBound(Ray, 1), UBound(Ray, 2)) = Ray(UBound(Ray, 1), _
                       UBound(Ray, 2)) + Ray(n, 4) - Ray(n, Ac)
                [COLOR="Navy"]Else[/COLOR]
                       Ray(n, Ac) = ""
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
                Ray(n, 4) = Ray(n, 4) * -1
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 1), UBound(Ray, 2))
    .Value = Ray
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[excel90210]

Many thanks Mick - really helpful. I will give this a try.

 

[excel90210]

I get 'Run-time error '429': ActiveX component can't create object'. If I click debug, the line:
With CreateObject("scripting.dictionary") is highlighted. I'm running on a MacBook Pro - MS Excel for Mac 2011 - version 14.5.8 (151023). I have tried to google a solution but can't find anything - thanks in advance.

 

[MickG]

Unfortunately the "Scripting dictionary" will not run on a "Mac"