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]

I think I may have to go with a formulaic solution

if you're wanting to just do the math without creating a new sheet etc
you just use the =vlookup to do math equations in column O in the PM sheet
so put the rider numbers in column A for both sheets (cut/insert)
then do something like

=IFERROR(K2-VLOOKUP(A2,AM!$1:$1048576,11,FALSE),"Vlookup Failed")

this will not show the movement of funds. however, so you can always add those columns and use

for funding area

=vlookup(A2,AM!$1:$1048576,2,false)

just change the "2" right before "false" to the respective column that you want to grab from for other values

 

[DataBlake]

hopefully that helps with the formula solution
if you ever get out of the weeds feel free to message me and we'll work on automating the processes for you

 

[Johnny4strings]

HI I am going to PM you later today-id definitely like to learn this more. I appreciate your help, it makes a difference and its appreciated.

 

[Johnny4strings]

Hey BlakeSkate! Check you inbox-sent you an example of my work in excel...much clearer images

 

[DataBlake]

Hey BlakeSkate! Check you inbox-sent you an example of my work in excel...much clearer images

@Johnny4strings
your inbox is full

 

[Johnny4strings]

OK im clear now, thanks for checking back with me!

 

[DataBlake]

OK im clear now, thanks for checking back with me!

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("L:M").Insert Shift:=xlToRight
    .Columns("O:P").Insert Shift:=xlToRight
    .Range("A1").Value = "From Funding"
    .Range("B1").Value = "To Funding"
    .Range("K1").Value = "AM Amount"
    .Range("L1").Value = "PM Amount"
    .Range("M1").Value = "Amount Difference"
    .Range("N1").Value = "AM Docket"
    .Range("O1").Value = "PM Docket"
    .Range("P1").Value = "Docket 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("L2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],PM!C1:C11,10,FALSE)"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("L2").AutoFill Destination:=Range("L2:L" & lastROW)
    Columns("L:L").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("O2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],PM!C1:C11,11,FALSE)"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("O2").AutoFill Destination:=Range("O2:O" & lastROW)
    Columns("O:O").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("M2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
 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("P2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
 lastROW = Range("A" & Rows.Count).End(xlUp).Row
    Range("P2").AutoFill Destination:=Range("P2:P" & lastROW)
    Columns("P:P").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

' remove no difference rows
Columns("M:M").Select
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

    Columns("M:M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


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

i believe this is the final VBA we decided on? let me know if there are any issues after clearing your inbox again.

 

[Johnny4strings]

Hello sir-just a note- I have uploaded the txt file via drive.

 

[Johnny4strings]

Hi! Just wanted to let you know that I think Im good and also---thanks for your help!!!! Working with you was great!!!