Movement of Funds

[Johnny4strings]

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

 

[DataBlake]

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

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

 

[Johnny4strings]

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)

AM data run example

Funding Area

Doc

Rider Number

Date

Personnel

Vendor

Prog

Building

Narrative

Cash Amt

Till Balance

Trans1

Days

Last Paid Date

2C

YYZ

70RRFLOLTER12

15-Feb-19

PPPP Inc

99612

9934551

Agricultire Incentive

100000

149000

232475

55

PM data run example

2D

YYZ

70RRFLOLTER12

15-Feb-19

PPPP Inc

99612

9934551

Agricultire Incentive

100000

100000

232475

55

Goal: Where did funding go and how much was transferred from?

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Johnny4strings]

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.

 

[DataBlake]

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.

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?

 

[Johnny4strings]

...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.

 

[DataBlake]

...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.

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?

From Funding

To Funding

Rider Number

Date

Personnel

Vendor

Prog

Building

Narrative

Cash amt

AM

PM

Difference

Trans1

Days

Last Paid Date

2C

2D

YYZ

15-Feb-19

PPPP Inc

99612

9934551

Agricultire Incentive

100000

149000

100000

-49000

232475

55

<tbody>
</tbody>

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

 

[Johnny4strings]

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

 

[DataBlake]

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

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

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

 

[Johnny4strings]

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