Calling macro experts

[bigmacfann]

Hello Macro geniuses! I have an easy one for you.
Each month, I get a report from Quickbooks which has all the details from each deposit we made in the previous month, it looks like this:

Deposit Detail Report - Mr Excel.xlsx
	A	B	C	D	E	F	G
1		Type	Date	Num	Name	Account	Amount
2
3		Deposit	08/02/2023			103 · Bank 3	13,575.80
4
5		Payment	08/02/2023	5262	Project 1	1005 · Undeposited Funds	-185.00
6		Payment	08/02/2023	5263	Project 2	1005 · Undeposited Funds	-145.00
7		Payment	08/02/2023	5264	Project 3	1005 · Undeposited Funds	-145.00
8		Payment	08/02/2023	10693	Project 4	1005 · Undeposited Funds	-24.25
9		Payment	08/02/2023	16835	Project 5	1005 · Undeposited Funds	-2,614.64
10		Payment	08/02/2023	142906	Project 6	1005 · Undeposited Funds	-1,250.00
11		Payment	08/02/2023	16855	Project 7	1005 · Undeposited Funds	-590.81
12		Payment	08/02/2023	530214	Project 8	1005 · Undeposited Funds	-322.40
13		Payment	08/02/2023	530214	Project 9	1005 · Undeposited Funds	-2,946.70
14		Payment	08/02/2023	25166	Project 10	1005 · Undeposited Funds	-285.00
15		Payment	08/02/2023	7193729907	Project 11	1005 · Undeposited Funds	-690.00
16		Payment	08/02/2023	311308	Project 12	1005 · Undeposited Funds	-4,223.00
17		Payment	08/02/2023	1366	Project 13	1005 · Undeposited Funds	-154.00
18	TOTAL						-13,575.80
19
20		Deposit	08/09/2023	1762266930	Bank 4	104 · Bank 4	1,342.00
21
22						101 · Bank 1	-1,342.00
23	TOTAL						-1,342.00
24
25		Deposit	08/22/2023			102 · Bank 2	6,676.65
26
27					Project 14	435 · Insurance - Worker's Comp	-34.50
28		Payment	08/22/2023	0000004763	Project 15	1005 · Undeposited Funds	-228.00
29		Payment	08/22/2023	7194160880	Project 16	1005 · Undeposited Funds	-598.50
30		Payment	08/22/2023	212382	Project 17	1005 · Undeposited Funds	-1,977.25
31		Payment	08/22/2023	1980	Project 18	1005 · Undeposited Funds	-192.50
32		Payment	08/22/2023	9710	Project 19	1005 · Undeposited Funds	-172.50
33		Payment	08/22/2023	0000004757	Project 20	1005 · Undeposited Funds	-1,069.50
34		Payment	08/22/2023	13023	Project 21	1005 · Undeposited Funds	-2,403.90
35	TOTAL						-6,676.65
36
37		Deposit	08/22/2023	1474631884	Bank 4	104 · Bank 4	1,544.70
38
39						101 · Bank 1	-1,544.70
40	TOTAL						-1,544.70
41
42		Deposit	08/30/2023			103 · Bank 3	11,475.96
43
44		Payment	08/30/2023	23005	Project 22	1005 · Undeposited Funds	-236.80
45		Payment	08/30/2023	17006	Project 23	1005 · Undeposited Funds	-5,329.10
46		Payment	08/30/2023	17006	Project 24	1005 · Undeposited Funds	-85.50
47		Payment	08/30/2023	16981	Project 25	1005 · Undeposited Funds	-844.56
48		Payment	08/30/2023	17255	Project 26	1005 · Undeposited Funds	-1,154.79
49		Payment	08/30/2023	25221	Project 27	1005 · Undeposited Funds	-148.00
50		Payment	08/30/2023	25221	Project 28	1005 · Undeposited Funds	-358.00
51		Payment	08/30/2023	532773	Project 29	1005 · Undeposited Funds	-147.80
52		Payment	08/30/2023	532773	Project 30	1005 · Undeposited Funds	-1,915.80
53		Payment	08/30/2023	16936	Project 31	1005 · Undeposited Funds	-817.13
54		Payment	08/30/2023	22346	Project 32	1005 · Undeposited Funds	-438.48
55	TOTAL						-11,475.96
56
57		Deposit	08/31/2023	#23-007-013	Bank 3	103 · Bank 3	24.50
58
59					Project 33	408 · Outside Drafting Services	-24.50
60	TOTAL						-24.50
Sheet1

Cell Formulas
Range	Formula
G18	G18	=ROUND(SUM(G4:G17),5)
G23,G60,G40	G23	=ROUND(SUM(G21:G22),5)
G35	G35	=ROUND(SUM(G26:G34),5)
G55	G55	=ROUND(SUM(G43:G54),5)

Keep in mind, this report will be different every month with varying amounts of deposit transactions at each of the 4 banks. For this report every month, I do not need the information for 'Bank 1' or 'Bank 4' which comes from Quickbooks, I only need the transactions which went into 'Bank 2' and 'Bank 3.' This is how the sheet should look once the macro completes:

Deposit Detail Report - Mr Excel.xlsx
	A	B	C
1	Date	Name	Amount
2	08/02/2023	Project 1	-185.00
3	08/02/2023	Project 2	-145.00
4	08/02/2023	Project 3	-145.00
5	08/02/2023	Project 4	-24.25
6	08/02/2023	Project 5	-2,614.64
7	08/02/2023	Project 6	-1,250.00
8	08/02/2023	Project 7	-590.81
9	08/02/2023	Project 8	-322.40
10	08/02/2023	Project 9	-2,946.70
11	08/02/2023	Project 10	-285.00
12	08/02/2023	Project 11	-690.00
13	08/02/2023	Project 12	-4,223.00
14	08/02/2023	Project 13	-154.00
15	08/22/2023	Project 14	-34.50
16	08/22/2023	Project 15	-228.00
17	08/22/2023	Project 16	-598.50
18	08/22/2023	Project 17	-1,977.25
19	08/22/2023	Project 18	-192.50
20	08/22/2023	Project 19	-172.50
21	08/22/2023	Project 20	-1,069.50
22	08/22/2023	Project 21	-2,403.90
23	08/30/2023	Project 22	-236.80
24	08/30/2023	Project 23	-5,329.10
25	08/30/2023	Project 24	-85.50
26	08/30/2023	Project 25	-844.56
27	08/30/2023	Project 26	-1,154.79
28	08/30/2023	Project 27	-148.00
29	08/30/2023	Project 28	-358.00
30	08/30/2023	Project 29	-147.80
31	08/30/2023	Project 30	-1,915.80
32	08/30/2023	Project 31	-817.13
33	08/30/2023	Project 32	-438.48
34	08/31/2023	Project 33	-24.50
Sheet1 (2)

Hopefully this is pretty easy but if you have questions, please let me know.

Thank you!

 

[jolivanes]

I am not an expert but following might do the job for you until a real expert comes around.
Try so.
Change references where required.

Sub Maybe()
Dim lr As Long
lr = Range("A:G").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
Application.ScreenUpdating = False
    With Range("A1:G" & lr)
        .AutoFilter Field:=5, Criteria1:="Project" & "*"
            .Copy Sheets("Sheet2").Range("A1")
        .AutoFilter
    End With
Sheets("Sheet2").Range("A:A, B:B, D:D, F:F").Delete
Application.ScreenUpdating = True
End Sub

 

[bigmacfann]

Thank you jolivanes for this code! It didn't perform exactly as I was hoping. I had to make 1 modification to get it to run: I had to change Sheet2 to Sheet1 since that is the label of my worksheet.

The code did consolidate the data down to the 3 columns, but it didn't delete the transactions relating to Bank 1 and Bank 4 nor delete the blank lines.

Is someone else able to take a shot at it?

Thank

 

[jolivanes]

Re: "I had to change Sheet2 to Sheet1" Hence the "Change references where required". Should be self explanatory.

Re: "Is someone else able to take a shot at it?" I hope someone else will.

 

[dreid1011]

Give this a try, in a new module:

Edit: Just a side note, this is designed to work specifically with the formatting displayed in your sample only. If there are ever any changes to the formatting, the code may not function as desired.

Sub Transactions()
Dim wsIn As Worksheet, wsOut As Worksheet
Set wsIn = Sheets("Input")      'change your input sheet name here
Set wsOut = Sheets("Output")    'change your destination sheet name here
Dim lr&, nr&, i&, j&, d&, amt&
lr = wsIn.Range("G" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

With wsIn
    .Range("D:D").Delete Shift:=xlLeft
    .Range("A:B").Delete Shift:=xlLeft
End With

For i = 1 To lr
    If wsIn.Range("A" & i).Value <> "" Then
'Change the bank names in the following line, will need to add or remove code if amount of banks <> 2
        If InStr(1, wsIn.Range("C" & i).Value, "Bank 3") Or InStr(1, wsIn.Range("C" & i).Value, "Bank 2") Then
            d = wsIn.Range("A" & i).Value
            amt = wsIn.Range("D" & i).Value
            j = i + 2
            Do While amt > 0
                With wsOut
                    nr = .Range("A" & Rows.Count).End(xlUp).Row + 1
                    .Range("A" & nr).Value = d
                    .Range("B" & nr).Value = wsIn.Range("B" & j).Value
                    .Range("C" & nr).Value = wsIn.Range("D" & j).Value
                End With
                amt = amt - Abs(wsIn.Range("D" & j).Value)
                j = j + 1
            Loop
        End If
    End If
Next i

Application.ScreenUpdating = True
End Sub