Movement of Funds

J

Johnny4strings

New Member
Joined
Nov 9, 2018
Messages
33
Hello everyone,


I am working on a project in which I have two lists of data. I will be pasting these in a workbook twice a day to see how much the dollar amount changes by organization between the morning and afternoon import of data. The first list I will have access to in the morning and the second list I will have access in the afternoon with the goal being to see what changes, monetarily between the two time frames. The lists will be formatted identically.


Since the data has many organizations listed in it, I would like assistance building something that both filters by organization as well as ties back to the original transaction; this is because not only do I have to depict the differences in funding between the two lists, I have to also identify how the funding moved (I have a organization code to track that, that I can use for a V lookup?) Please see below for an example:


Example:
Morning Data run
Org 2A, project code=11, amount $100


Evening Data run
Org 2A, project code=12, amount $100
balance in Org 2A=$100, project code 11=$0
balance in Org 2A=$100, project code 12=$100


I appreciate any suggestions!
John
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Johnny4strings said:
Hello everyone,


I am working on a project in which I have two lists of data. I will be pasting these in a workbook twice a day to see how much the dollar amount changes by organization between the morning and afternoon import of data. The first list I will have access to in the morning and the second list I will have access in the afternoon with the goal being to see what changes, monetarily between the two time frames. The lists will be formatted identically.


Since the data has many organizations listed in it, I would like assistance building something that both filters by organization as well as ties back to the original transaction; this is because not only do I have to depict the differences in funding between the two lists, I have to also identify how the funding moved (I have a organization code to track that, that I can use for a V lookup?) Please see below for an example:


Example:
Morning Data run
Org 2A, project code=11, amount $100


Evening Data run
Org 2A, project code=12, amount $100
balance in Org 2A=$100, project code 11=$0
balance in Org 2A=$100, project code 12=$100


I appreciate any suggestions!
John
Click to expand...

Could you possibly create a table of example data? obviously fudge numbers and names, but try to be a specific about it as you can & include any headers.
the example is a little foreign to me otherwise.
by "I have to depict the differences in funding between the two lists" you mean that Org 2A has $100 in the morning but in the evening it has $50 and you just want to show that -50 in another column or something?
I'm also not sure what the project code is?
but yeah a table with the likeness of your data would help immensely
 
Upvote 0
i and thanks so much for the response. Below is a table-the focus is on The funding area, far left and the till balance (moving from 149000 to 100000)

[TABLE="width: 1299"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]AM data run example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Funding Area[/TD]
[TD]Doc[/TD]
[TD]Rider Number[/TD]
[TD]Date[/TD]
[TD]Personnel[/TD]
[TD]Vendor[/TD]
[TD]Prog[/TD]
[TD]Building[/TD]
[TD]Narrative[/TD]
[TD]Cash Amt[/TD]
[TD]Till Balance[/TD]
[TD]Trans1[/TD]
[TD]Days[/TD]
[TD]Last Paid Date[/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD]YYZ[/TD]
[TD]70RRFLOLTER12[/TD]
[TD]15-Feb-19[/TD]
[TD][/TD]
[TD]PPPP Inc[/TD]
[TD]99612[/TD]
[TD]9934551[/TD]
[TD]Agricultire Incentive[/TD]
[TD]100000[/TD]
[TD]149000[/TD]
[TD]232475[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PM data run example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2D[/TD]
[TD]YYZ[/TD]
[TD]70RRFLOLTER12[/TD]
[TD]15-Feb-19[/TD]
[TD][/TD]
[TD]PPPP Inc[/TD]
[TD]99612[/TD]
[TD]9934551[/TD]
[TD]Agricultire Incentive[/TD]
[TD]100000[/TD]
[TD]100000[/TD]
[TD]232475[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Goal: Where did funding go and how much was transferred from? [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Looks like to me the "Rider Number" would be a key identifying feature that we may could use to anchor the movement of funds-it is unique among the other records that are provided in the list.
 
Upvote 0
Johnny4strings said:
Looks like to me the "Rider Number" would be a key identifying feature that we may could use to anchor the movement of funds-it is unique among the other records that are provided in the list.
Click to expand...

are the AM and PM sheets identical other than maybe the order and the till balance?
matching the rider number won't be difficult, but if i don't have to thats better/faster
I.E there are the same number of organizations / rider numbers correct?
 
Last edited:
Upvote 0
...hello,
Yes the sheets will have the same number of columns and heading titles....
I think what I have to do is match on the rider number to follow the change of funds from funding area 2C to 2D in the amount of $49000 as in this example.
 
Upvote 0
Johnny4strings said:
...hello,
Yes the sheets will have the same number of columns and heading titles....
I think what I have to do is match on the rider number to follow the change of funds from funding area 2C to 2D in the amount of $49000 as in this example.
Click to expand...

okay so lets say you paste your morning sheet into a sheet named "AM" and evening in a sheet named "PM"
you would like to run a macro that creates a new sheet that uses the rider number to show that from 2C to 2D there was a -49000 difference?
thats a lot simpler than i thought you were asking if thats the case

if thats confirmed i just have one more question about "building something that both filters by organization as well as ties back to the original transaction"
were you wanting this new sheet to have all present data in both sheets w/ filters that show by org name?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]From Funding[/TD]
[TD]To Funding[/TD]
[TD]Rider Number[/TD]
[TD]Date[/TD]
[TD]Personnel[/TD]
[TD]Vendor[/TD]
[TD]Prog[/TD]
[TD]Building[/TD]
[TD]Narrative[/TD]
[TD]Cash amt[/TD]
[TD]AM[/TD]
[TD]PM[/TD]
[TD]Difference[/TD]
[TD]Trans1[/TD]
[TD]Days[/TD]
[TD]Last Paid Date[/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD]2D[/TD]
[TD]YYZ[/TD]
[TD]15-Feb-19[/TD]
[TD][/TD]
[TD]PPPP Inc[/TD]
[TD]99612[/TD]
[TD]9934551[/TD]
[TD]Agricultire Incentive[/TD]
[TD]100000[/TD]
[TD]149000[/TD]
[TD]100000[/TD]
[TD]-49000[/TD]
[TD]232475[/TD]
[TD]55[/TD]
[/TR]
</tbody>[/TABLE]

This is what that new sheet would look like after you run the macro & the workbook would countain a filter that lets you select vendors?
or if not create a table of your expected results
 
Last edited:
Upvote 0
Hi thanks for replying again!
I have to admit the idea of a macro didnt even register with me-I was planning on a V lookup or a Match since I am better with hard-coded formulas than VBA (though I can try a macro).
What you are proposing is spot on, just trying to track the movement of funds from 2C to 2D-and what took place within that transfer, which you got in your table. For the other funding areas I have to work in, I can modify the marco to filter on the different naming conventions they have, e.g. 2K, 3A, etc. I already have two tabs set up for pasting the data, one for the AM and one for the PM. I think we can make what you are proposing to work!
John
 
Upvote 0
Johnny4strings said:
Hi thanks for replying again!
I have to admit the idea of a macro didnt even register with me-I was planning on a V lookup or a Match since I am better with hard-coded formulas than VBA (though I can try a macro).
What you are proposing is spot on, just trying to track the movement of funds from 2C to 2D-and what took place within that transfer, which you got in your table. For the other funding areas I have to work in, I can modify the marco to filter on the different naming conventions they have, e.g. 2K, 3A, etc. I already have two tabs set up for pasting the data, one for the AM and one for the PM. I think we can make what you are proposing to work!
John
Click to expand...

Cool!
as far as macros are concerned why manually do anything when you can automate the process? right?

As long as the sheet names are "AM" and "PM" and your data remains in those columns i created this macro
Code: 
Sub difference()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim lastROW As Long
Dim ps As Worksheet
Dim ams As Worksheet
Dim pms As Worksheet

Set ams = wb.Sheets("AM")
Set pms = wb.Sheets("PM")

' duplicate sheet AM
ams.Copy ThisWorkbook.Sheets(Sheets.Count)

    
' declare sheet
ActiveSheet.Range("A1").Select
ActiveSheet.Name = "Processed"
Set ps = wb.Sheets("Processed")

' move rider number for vlookup
pms.Select
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ams.Select
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

' insert rows and new headers
ps.Select
With ActiveSheet
    .Columns("B:B").Insert Shift:=xlToRight
    .Columns("M:N").Insert Shift:=xlToRight
    .Range("A1").Value = "From Funding"
    .Range("B1").Value = "To Funding"
    .Range("L1").Value = "AM Balance"
    .Range("M1").Value = "PM Balance"
    .Range("N1").Value = "Difference"
End With
    
'insert vlookup and match rows to lastROW
 
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],PM!C1:C2,2,FALSE)"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2").AutoFill Destination:=Range("B2:B" & lastROW)
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],PM!C1:C11,11,FALSE)"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("M2").AutoFill Destination:=Range("M2:M" & lastROW)
    Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("N2").AutoFill Destination:=Range("N2:N" & lastROW)
    Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
' undo rider number move
pms.Select
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
ams.Select
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
End Sub

let me know if there are any issues with it
2 sheets "AM"/"PM" are the only requirements
it makes the new processed sheet for you
 
Last edited:
Upvote 0
Hi I wanted to firstly thank you for your hard work. I changed my tabs to AM and PM, though the macro broke after I inserted it into a new module.
The error was run code 1004, this is after the selections of columns A-D; will bold where the debugger caught errors-it looks like its getting stuck on creating the new sheet?
.....................................................................................

Sub difference()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim lastROW As Long
Dim ps As Worksheet
Dim ams As Worksheet
Dim pms As Worksheet


Set ams = wb.Sheets("AM")
Set pms = wb.Sheets("PM")


' duplicate sheet AM
ams.Copy ThisWorkbook.Sheets(Sheets.Count)



' declare sheet
ActiveSheet.Range("A1").Select
ActiveSheet.Name = "Processed"
Set ps = wb.Sheets("Processed")


' move rider number for vlookup
pms.Select
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ams.Select
Columns("C:C").Select
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight


' insert rows and new headers
ps.Select
With ActiveSheet
.Columns("B:B").Insert Shift:=xlToRight
.Columns("M:N").Insert Shift:=xlToRight
.Range("A1").Value = "From Funding"
.Range("B1").Value = "To Funding"
.Range("L1").Value = "AM Balance"
.Range("M1").Value = "PM Balance"
.Range("N1").Value = "Difference"
End With

'insert vlookup and match rows to lastROW

Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],PM!C1:C2,2,FALSE)"
lastROW = Range("A" & Rows.Count).End(xlUp).Row
Range("B2").AutoFill Destination:=Range("B2:B" & lastROW)
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("M2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-9],PM!C1:C11,11,FALSE)"
lastROW = Range("A" & Rows.Count).End(xlUp).Row
Range("M2").AutoFill Destination:=Range("M2:M" & lastROW)
Columns("M:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
lastROW = Range("A" & Rows.Count).End(xlUp).Row
Range("N2").AutoFill Destination:=Range("N2:N" & lastROW)
Columns("N:N").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' undo rider number move
pms.Select
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
ams.Select
Columns("A:A").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
End Sub
.................................................


Again, thanks so much for the help-but it was just laid on me that I have to now do this for five separate tabs as they are introducing new funding codes. I think I may have to go with a formulaic solution as I have to repeat this macro process now for 5 tabs-and Im in the weeds with this one though it does make sense to work with a macro.
Definitely open to suggestions...
John
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
159
BenW71
B
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
390
Jagadeshrao
J
A
  • Question Question
banding by value
Replies
4
Views
72
ajm
A
D
  • Question Question
Remove matching rows with pause function
Replies
1
Views
188
alansidman
alansidman
T
  • Solved
Move cursor to a specific cell after data has been entered
Replies
4
Views
409
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

Share this page

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
Back
Top