Separate Downloaded Report Data into Separate Tabs or Workbooks?

[NewFrugal]

Hello; I genuinely appreciate your help in advance.

Monthly, I download a report with almost 40 departments that each need a separate report. The accounting system I use does not make this an easy task, as I have to manually copy each section of the report (each department) into a new sheet or copy the master sheet 40 times and delete all other departments from the sheet. For example, I have to create a new tab for Dept. 115 and delete all department numbers (and their respective data) before it and after it. This is too time-consuming and cumbersome. Is there a way that I can extract data into separate tabs or workbooks based on the department number? In essence, I would want the tabs to be named to the department number that is pulled, and the tab would only pull its respective information. I'm not sure if I'm making much sense, but here's a mini sheet of how the data is downloaded (example with about three departments). This is exactly how the report appears when downloaded. If there is some sort of VBA necessary, could you please help me by providing it? VBA is not my forte at all. Thanks again.

Feb. 2023 Department Summaries.xlsx
	A	B	C	D	E	F	G	H	I
1	Hogwarts School
2	2022-23 Department Report
3
4				Current Year YTD 02/28/2023	Budget 2022-23 06/30/2023	2022-23 Budget Remaining	Prior Year YTD 06/30/2022
5	100 - Classics
6	Expenses
7
8	01-6200-100	Resource Supplies-Classics		$940.95	$1,640.00	$699.05	$1,312.20
9	01-7045-100	Office Supplies-Classics		$0.00	$0.00	$0.00	$25.00
10	Total Expenses			$940.95	$1,640.00	$699.05	$1,337.20
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43	03/14/2023 4:09:46 PM							Page 1
44
45	Hogwarts School
46	2022-23 Department Report
47
48				Current Year YTD 02/28/2023	Budget 2022-23 06/30/2023	2022-23 Budget Remaining	Prior Year YTD 06/30/2022
49	101 - Computer Science
50	Expenses
51
52	01-6200-101	Resource Supplies-Computer Science		$0.00	$1,200.00	$1,200.00	$0.00
53	Total Expenses			$0.00	$1,200.00	$1,200.00	$0.00
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87	03/14/2023 4:09:46 PM							Page 2
88
89	Hogwarts School
90	2022-23 Department Report
91
92				Current Year YTD 02/28/2023	Budget 2022-23 06/30/2023	2022-23 Budget Remaining	Prior Year YTD 06/30/2022
93	102 - English
94	Expenses
95
96	01-6200-102	Resource Supplies-English		$904.10	$2,350.00	$1,445.90	$2,491.79
97	01-7045-102	Office Supplies-English		$422.74	$150.00	($272.74)	$209.46
98	Total Expenses			$1,326.84	$2,500.00	$1,173.16	$2,701.25
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131	03/14/2023 4:09:46 PM							Page 3
132
133	Hogwarts School
134	2022-23 Department Report
135
136				Current Year YTD 02/28/2023	Budget 2022-23 06/30/2023	2022-23 Budget Remaining	Prior Year YTD 06/30/2022
137	103 - Fine Arts
138	Expenses
139
140	01-6215-103	Art Supplies-Fine Arts		$3,202.36	$10,000.00	$6,797.64	$10,851.32
141	01-6220-103	Music Supplies-Fine Arts		$4,853.00	$15,000.00	$10,147.00	$197.65
142	01-6225-103	Band-Fine Arts		$2,463.39	$14,000.00	$11,536.61	$4,745.29
143	01-6230-103	Choral Arts-Fine Arts		$8,438.18	$12,500.00	$4,061.82	$6,623.92
144	01-7135-103	Photography-Fine Arts		$0.00	$8,000.00	$8,000.00	$2,044.20
145	Total Expenses			$18,956.93	$59,500.00	$40,543.07	$24,462.38
XTRA

 

[NewFrugal]

You are very welcome.

Sorry to bother you again, mumps. Would you mind helping me with the VBA for the sample below, please? This report is a detailed version of the one from yesterday (which was a summary report), and it doesn't look like anything is merged, but I didn't thoroughly check, and there are extra columns. I need this report to perform the same functions as the summary report by pulling data for each department into its own tab with the tab renamed to the department number and name. I sincerely appreciate your expertise!!

MrExcel Detail Report Sample.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Hogwarts School
2	2022-23 Department Detail Report
3
4	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
5	100 - Classics
6	Account:	01-6200-100 (Resource Supplies-Classics)
7	07/01/2022	Account Beginning Balance							$0.00
8	02/27/2023	1023-327	Accounts Payable		DOBBY-none-022323	$940.95				0000
9						$940.95	$0.00
10	02/28/2023	Account Net Change							$940.95
11	02/28/2023	Account Ending Balance							$940.95
12	Account:	01-7045-100 (Office Supplies-Classics)
13	07/01/2022	Account Beginning Balance							$0.00
14	12/08/2022	0-28	Cash Receipts		Unposted Cash Management Adjustment		$10.00			0000
15	12/15/2022	1023-359	Accounts Payable		BLACKBELT-	$10.00				0000
16						$10.00	$10.00
17	02/28/2023	Account Net Change							$0.00
18	02/28/2023	Account Ending Balance							$0.00
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43	03/24/2023 10:33:32 AM								Page 1
44
45	Hogwarts School
46	2022-23 Department Detail Report
47
48	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
49	102 - English
50	Account:	01-6200-102 (Resource Supplies-English)
51	07/01/2022	Account Beginning Balance							$0.00
52	11/03/2022	909-69	Accounts Payable		HERMIONE GRANGER-none-101822	$199.95				0000
53	11/09/2022	911-95	Accounts Payable		HERMIONE GRANGER-none-110822	$35.44				0000
54	11/22/2022	931-40	Accounts Payable		HERMIONE GRANGER-3257-111822	$123.48				0000
55	12/08/2022	931-274	Accounts Payable		LUNA LOVEGOOD-3257-120122	$294.44				0000
56	12/15/2022	943-184	Accounts Payable		HERMIONE GRANGER-3257-121222	$56.32				0000
57	12/22/2022	951-42	Accounts Payable		RON WEASLEY-REIMB 121222	$73.24				0000
58	01/11/2023	961-187	Accounts Payable		HERMIONE GRANGER-none-010423	$29.00				0000
59	01/11/2023	961-249	Accounts Payable		ALBUS DUMBLEDORE-3257-010923	$31.25				0000
60	01/11/2023	961-251	Accounts Payable		HARRY POTTER-none-010623	$60.98				0000
61						$904.10	$0.00
62	02/28/2023	Account Net Change							$904.10
63	02/28/2023	Account Ending Balance							$904.10
64	Account:	01-7045-102 (Office Supplies-English)
65	07/01/2022	Account Beginning Balance							$0.00
66	08/29/2022	829-134	Accounts Payable		HERMIONE GRANGER-REIMBURSEMENT 083022	$337.89				0000
67	08/31/2022	889-8	General Ledger		TARGET.COM	$84.85				0000
68						$422.74	$0.00
69	02/28/2023	Account Net Change							$422.74
70	02/28/2023	Account Ending Balance							$422.74
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87	03/24/2023 10:33:32 AM								Page 2
88
89	Hogwarts School
90	2022-23 Department Detail Report
91
92	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
93	103 - Fine Arts
94	Account:	01-6215-103 (Art Supplies-Fine Arts)
95	07/01/2022	Account Beginning Balance							$0.00
96	10/28/2022	904-40	Accounts Payable		RON WEASLEY-3257-102122	$150.38				0000
97	11/09/2022	911-105	Accounts Payable		RON WEASLEY-102122	$168.71				0000
98	01/19/2023	984-174	Accounts Payable		JAMES POTTER-REIM 011923	$2,883.27				0000
99						$3,202.36	$0.00
100	02/28/2023	Account Net Change							$3,202.36
101	02/28/2023	Account Ending Balance							$3,202.36
102	Account:	01-6220-103 (Music Supplies-Fine Arts)
103	07/01/2022	Account Beginning Balance							$0.00
104	07/24/2022	770-33	Accounts Payable		Pitch Perfect Tuning-PIANOLOCKS 081722	$648.00				CLUBS1012
105	11/30/2022	1015-61	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232	$4,205.00				0000
106						$4,853.00	$0.00
107	02/28/2023	Account Net Change							$4,853.00
108	02/28/2023	Account Ending Balance							$4,853.00
109	Account:	01-6225-103 (Band-Fine Arts)
110	07/01/2022	Account Beginning Balance							$0.00
111	09/13/2022	1005-1	Accounts Payable		Newt Scamander-	$79.50				0000
112	09/15/2022	851-74	Accounts Payable		Newt Scamander-3257-091322	$79.50				0000
113	09/16/2022	859-55	Accounts Payable		SALT & PEPPER, INC-364460625	$77.99				0000
114	09/16/2022	859-71	Accounts Payable		SALT & PEPPER, INC-364464325	$67.00				0000
115	09/16/2022	859-81	Accounts Payable		SALT & PEPPER, INC-364485626	$115.00				0000
116	09/16/2022	859-97	Accounts Payable		SALT & PEPPER, INC-364502224	$45.99				0000
117	09/16/2022	859-201	Accounts Payable		SALT & PEPPER, INC-364383278	$57.99				0000
118	09/16/2022	859-203	Accounts Payable		SALT & PEPPER, INC-364400557	$40.00				0000
119	09/16/2022	859-205	Accounts Payable		SALT & PEPPER, INC-364381861	$13.00				0000
120	09/16/2022	859-207	Accounts Payable		SALT & PEPPER, INC-364074730	$196.99				0000
121	09/16/2022	859-209	Accounts Payable		SALT & PEPPER, INC-364184267	$4.99				0000
122	09/16/2022	859-211	Accounts Payable		SALT & PEPPER, INC-364262591	$45.00				0000
123	09/16/2022	859-213	Accounts Payable		SALT & PEPPER, INC-364185501	$152.99				0000
124	09/16/2022	859-215	Accounts Payable		SALT & PEPPER, INC-364380865	$124.99				0000
125	09/16/2022	859-217	Accounts Payable		SALT & PEPPER, INC-364184783	$2.99				0000
126	09/16/2022	859-219	Accounts Payable		SALT & PEPPER, INC-364171249	$60.00				0000
127	09/16/2022	859-221	Accounts Payable		SALT & PEPPER, INC-364049735	$49.95				0000
128	09/16/2022	859-223	Accounts Payable		SALT & PEPPER, INC-364015100	$130.00				0000
129	09/16/2022	859-225	Accounts Payable		SALT & PEPPER, INC-364045885	$121.99				0000
130
131	03/24/2023 10:33:32 AM								Page 3
132
133	Hogwarts School
134	2022-23 Department Detail Report
135
136	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
137	Account:	01-6225-103 (Band-Fine Arts)
138	09/21/2022	889-219	General Ledger		MUSIC ON THE HILL	$121.87				0000
139	09/26/2022	889-331	General Ledger		VENMO	$309.00				0000
140	10/22/2022	944-247	General Ledger		FOXES MUSIC CO	$31.81				0000
141	10/25/2022	944-276	General Ledger		WM SUPERCENTER #5941	$6.96				0000
142	10/25/2022	944-277	General Ledger		UNOCHICAGOGRILL#701	$562.39				0000
143	11/09/2022	911-93	Accounts Payable		SALT & PEPPER, INC-364744103	$45.00				0000
144	12/21/2022	941-134	General Ledger		AvidPay credit (ck #95054, A. Wood)		$79.50			0000
145						$2,542.89	$79.50
146	02/28/2023	Account Net Change							$2,463.39
147	02/28/2023	Account Ending Balance							$2,463.39
148	Account:	01-6230-103 (Choral Arts-Fine Arts)
149	07/01/2022	Account Beginning Balance							$0.00
150	07/29/2022	770-86	Accounts Payable		SALT & PEPPER, INC-364280581	$202.82				0000
151	07/29/2022	775-329	General Ledger		SALT & PEPPER, INC-364280581		$202.82			0000
152	08/09/2022	773-145	Accounts Payable		SALT & PEPPER, INC-364353241	$35.98				0000
153	08/09/2022	775-342	General Ledger		SALT & PEPPER, INC-364353241		$35.98			0000
154	09/14/2022	889-158	General Ledger		SALT & PEPPER, INC-364899871	$262.94				0000
155	09/15/2022	851-60	Accounts Payable		Ginny Weasley-3257-083122	$401.17				0000
156	09/15/2022	851-80	Accounts Payable		Ginny Weasley-3257-091322	$193.84				0000
157	09/15/2022	889-170	General Ledger		SALT & PEPPER, INC-364899871	$50.70				0000
158	09/19/2022	889-197	General Ledger		WWW.ACDA.ORG	$125.00				0000
159	09/21/2022	889-224	General Ledger		GIA PUBLICATIONS INC	$64.59				0000
160	10/13/2022	944-125	General Ledger		SQ SWEET SPOT STUD	$385.00				0000
161	10/18/2022	944-178	General Ledger		SALT & PEPPER, INC-364899871	$5,318.87				0000
162	10/19/2022	944-188	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
163	10/19/2022	944-189	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
164	10/20/2022	944-215	General Ledger		SALT & PEPPER, INC-364899871	$49.99				0000
165	10/21/2022	944-233	General Ledger		STAPLES DIRECT	$44.51				0000
166	10/25/2022	944-284	General Ledger		STAPLES DIRECT	$287.09				0000
167	10/28/2022	904-30	Accounts Payable		SALT & PEPPER, INC-364569916	$125.00				0000
168	10/28/2022	904-32	Accounts Payable		SALT & PEPPER, INC-364582722	$64.00				0000
169	10/28/2022	904-34	Accounts Payable		SALT & PEPPER, INC-364645487	$58.99				0000
170	11/09/2022	911-65	Accounts Payable		KITT'S MUSIC-603739232	$4,205.00				0000
171	11/09/2022	911-101	Accounts Payable		SALT & PEPPER, INC-364683994	$286.99				0000
172	11/09/2022	911-103	Accounts Payable		SALT & PEPPER, INC-364691138	$56.00				0000
173	11/30/2022	1015-62	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232		$4,205.00			0000
174
175
176	03/24/2023 10:33:32 AM								Page 4
177
178	Hogwarts School
179	2022-23 Department Detail Report
180
181	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
182	Account:	01-6230-103 (Choral Arts-Fine Arts)
183	01/10/2023	1059-18	Accounts Payable		SALT & PEPPER, INC-	$166.97				0000
184	01/12/2023	961-269	Accounts Payable		Ginny Weasley-none-010322	$90.00				0000
185	01/12/2023	961-271	Accounts Payable		SALT & PEPPER, INC-364899871	$122.99				0000
186	01/12/2023	961-273	Accounts Payable		SALT & PEPPER, INC-364902908	$45.00				0000
187	01/18/2023	984-102	Accounts Payable		SALT & PEPPER, INC-364941825	$40.94				0000
188	01/19/2023	999-61	Cash Receipts		Choir-823-1418		$520.45			0000
189	01/31/2023	1005-25	Accounts Payable		FRANCESCA HURST-25	$350.00				0000
190	02/27/2023	1023-321	Accounts Payable		SALT & PEPPER, INC-365019499	$71.50				0000
191	02/27/2023	1023-323	Accounts Payable		SALT & PEPPER, INC-364980015	$227.56				0000
192	02/27/2023	1023-325	Accounts Payable		SALT & PEPPER, INC-365102855	$49.99				0000
193						$13,402.43	$4,964.25
194	02/28/2023	Account Net Change							$8,438.18
195	02/28/2023	Account Ending Balance							$8,438.18
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221	03/24/2023 10:33:32 AM								Page 5
222
XTRA

 

[mumps]

Macros depend very highly on regular patterns in how the data is organized. In the summary file, you will notice that the department name is always 4 rows below the row containing the text "Hogwarts School". The new file starts out with the same pattern but starting in row 136, there is no department name below the "Date" cell.

 

[NewFrugal]

Macros depend very highly on regular patterns in how the data is organized. In the summary file, you will notice that the department name is always 4 rows below the row containing the text "Hogwarts School". The new file starts out with the same pattern but starting in row 136, there is no department name below the "Date" cell.

Thank you for this valuable information. I'll have to figure out how I can download the data so that the department name consistently shows up and get back to you . I work with a very non-accountant-friendly accounting system that doesn't provide reports in a sensical manner. I might just have to go in and add a line with the department name.

 

[NewFrugal]

Hi Mumps. I went into the file and updated line 136. I really appreciate your help!

MrExcel Detail Report Sample.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Hogwarts School
2	2022-23 Department Detail Report
3
4	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
5	100 - Classics
6	Account:	01-6200-100 (Resource Supplies-Classics)
7	07/01/2022	Account Beginning Balance							$0.00
8	02/27/2023	1023-327	Accounts Payable		DOBBY-none-022323	$940.95				0000
9						$940.95	$0.00
10	02/28/2023	Account Net Change							$940.95
11	02/28/2023	Account Ending Balance							$940.95
12	Account:	01-7045-100 (Office Supplies-Classics)
13	07/01/2022	Account Beginning Balance							$0.00
14	12/08/2022	0-28	Cash Receipts		Unposted Cash Management Adjustment		$10.00			0000
15	12/15/2022	1023-359	Accounts Payable		BLACKBELT-	$10.00				0000
16						$10.00	$10.00
17	02/28/2023	Account Net Change							$0.00
18	02/28/2023	Account Ending Balance							$0.00
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43	03/24/2023 10:33:32 AM								Page 1
44
45	Hogwarts School
46	2022-23 Department Detail Report
47
48	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
49	102 - English
50	Account:	01-6200-102 (Resource Supplies-English)
51	07/01/2022	Account Beginning Balance							$0.00
52	11/03/2022	909-69	Accounts Payable		HERMIONE GRANGER-none-101822	$199.95				0000
53	11/09/2022	911-95	Accounts Payable		HERMIONE GRANGER-none-110822	$35.44				0000
54	11/22/2022	931-40	Accounts Payable		HERMIONE GRANGER-3257-111822	$123.48				0000
55	12/08/2022	931-274	Accounts Payable		LUNA LOVEGOOD-3257-120122	$294.44				0000
56	12/15/2022	943-184	Accounts Payable		HERMIONE GRANGER-3257-121222	$56.32				0000
57	12/22/2022	951-42	Accounts Payable		RON WEASLEY-REIMB 121222	$73.24				0000
58	01/11/2023	961-187	Accounts Payable		HERMIONE GRANGER-none-010423	$29.00				0000
59	01/11/2023	961-249	Accounts Payable		ALBUS DUMBLEDORE-3257-010923	$31.25				0000
60	01/11/2023	961-251	Accounts Payable		HARRY POTTER-none-010623	$60.98				0000
61						$904.10	$0.00
62	02/28/2023	Account Net Change							$904.10
63	02/28/2023	Account Ending Balance							$904.10
64	Account:	01-7045-102 (Office Supplies-English)
65	07/01/2022	Account Beginning Balance							$0.00
66	08/29/2022	829-134	Accounts Payable		HERMIONE GRANGER-REIMBURSEMENT 083022	$337.89				0000
67	08/31/2022	889-8	General Ledger		TARGET.COM	$84.85				0000
68						$422.74	$0.00
69	02/28/2023	Account Net Change							$422.74
70	02/28/2023	Account Ending Balance							$422.74
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87	03/24/2023 10:33:32 AM								Page 2
88
89	Hogwarts School
90	2022-23 Department Detail Report
91
92	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
93	103 - Fine Arts
94	Account:	01-6215-103 (Art Supplies-Fine Arts)
95	07/01/2022	Account Beginning Balance							$0.00
96	10/28/2022	904-40	Accounts Payable		RON WEASLEY-3257-102122	$150.38				0000
97	11/09/2022	911-105	Accounts Payable		RON WEASLEY-102122	$168.71				0000
98	01/19/2023	984-174	Accounts Payable		JAMES POTTER-REIM 011923	$2,883.27				0000
99						$3,202.36	$0.00
100	02/28/2023	Account Net Change							$3,202.36
101	02/28/2023	Account Ending Balance							$3,202.36
102	Account:	01-6220-103 (Music Supplies-Fine Arts)
103	07/01/2022	Account Beginning Balance							$0.00
104	07/24/2022	770-33	Accounts Payable		Pitch Perfect Tuning-PIANOLOCKS 081722	$648.00				CLUBS1012
105	11/30/2022	1015-61	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232	$4,205.00				0000
106						$4,853.00	$0.00
107	02/28/2023	Account Net Change							$4,853.00
108	02/28/2023	Account Ending Balance							$4,853.00
109	Account:	01-6225-103 (Band-Fine Arts)
110	07/01/2022	Account Beginning Balance							$0.00
111	09/13/2022	1005-1	Accounts Payable		Newt Scamander-	$79.50				0000
112	09/15/2022	851-74	Accounts Payable		Newt Scamander-3257-091322	$79.50				0000
113	09/16/2022	859-55	Accounts Payable		SALT & PEPPER, INC-364460625	$77.99				0000
114	09/16/2022	859-71	Accounts Payable		SALT & PEPPER, INC-364464325	$67.00				0000
115	09/16/2022	859-81	Accounts Payable		SALT & PEPPER, INC-364485626	$115.00				0000
116	09/16/2022	859-97	Accounts Payable		SALT & PEPPER, INC-364502224	$45.99				0000
117	09/16/2022	859-201	Accounts Payable		SALT & PEPPER, INC-364383278	$57.99				0000
118	09/16/2022	859-203	Accounts Payable		SALT & PEPPER, INC-364400557	$40.00				0000
119	09/16/2022	859-205	Accounts Payable		SALT & PEPPER, INC-364381861	$13.00				0000
120	09/16/2022	859-207	Accounts Payable		SALT & PEPPER, INC-364074730	$196.99				0000
121	09/16/2022	859-209	Accounts Payable		SALT & PEPPER, INC-364184267	$4.99				0000
122	09/16/2022	859-211	Accounts Payable		SALT & PEPPER, INC-364262591	$45.00				0000
123	09/16/2022	859-213	Accounts Payable		SALT & PEPPER, INC-364185501	$152.99				0000
124	09/16/2022	859-215	Accounts Payable		SALT & PEPPER, INC-364380865	$124.99				0000
125	09/16/2022	859-217	Accounts Payable		SALT & PEPPER, INC-364184783	$2.99				0000
126	09/16/2022	859-219	Accounts Payable		SALT & PEPPER, INC-364171249	$60.00				0000
127	09/16/2022	859-221	Accounts Payable		SALT & PEPPER, INC-364049735	$49.95				0000
128	09/16/2022	859-223	Accounts Payable		SALT & PEPPER, INC-364015100	$130.00				0000
129	09/16/2022	859-225	Accounts Payable		SALT & PEPPER, INC-364045885	$121.99				0000
130
131	03/24/2023 10:33:32 AM								Page 3
132
133	Hogwarts School
134	2022-23 Department Detail Report
135
136	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
137	103 - Fine Arts
138	Account:	01-6225-103 (Band-Fine Arts)
139	09/21/2022	889-219	General Ledger		MUSIC ON THE HILL	$121.87				0000
140	09/26/2022	889-331	General Ledger		VENMO	$309.00				0000
141	10/22/2022	944-247	General Ledger		FOXES MUSIC CO	$31.81				0000
142	10/25/2022	944-276	General Ledger		WM SUPERCENTER #5941	$6.96				0000
143	10/25/2022	944-277	General Ledger		UNOCHICAGOGRILL#701	$562.39				0000
144	11/09/2022	911-93	Accounts Payable		SALT & PEPPER, INC-364744103	$45.00				0000
145	12/21/2022	941-134	General Ledger		AvidPay credit (ck #95054, A. Wood)		$79.50			0000
146						$2,542.89	$79.50
147	02/28/2023	Account Net Change							$2,463.39
148	02/28/2023	Account Ending Balance							$2,463.39
149	Account:	01-6230-103 (Choral Arts-Fine Arts)
150	07/01/2022	Account Beginning Balance							$0.00
151	07/29/2022	770-86	Accounts Payable		SALT & PEPPER, INC-364280581	$202.82				0000
152	07/29/2022	775-329	General Ledger		SALT & PEPPER, INC-364280581		$202.82			0000
153	08/09/2022	773-145	Accounts Payable		SALT & PEPPER, INC-364353241	$35.98				0000
154	08/09/2022	775-342	General Ledger		SALT & PEPPER, INC-364353241		$35.98			0000
155	09/14/2022	889-158	General Ledger		SALT & PEPPER, INC-364899871	$262.94				0000
156	09/15/2022	851-60	Accounts Payable		Ginny Weasley-3257-083122	$401.17				0000
157	09/15/2022	851-80	Accounts Payable		Ginny Weasley-3257-091322	$193.84				0000
158	09/15/2022	889-170	General Ledger		SALT & PEPPER, INC-364899871	$50.70				0000
159	09/19/2022	889-197	General Ledger		WWW.ACDA.ORG	$125.00				0000
160	09/21/2022	889-224	General Ledger		GIA PUBLICATIONS INC	$64.59				0000
161	10/13/2022	944-125	General Ledger		SQ SWEET SPOT STUD	$385.00				0000
162	10/18/2022	944-178	General Ledger		SALT & PEPPER, INC-364899871	$5,318.87				0000
163	10/19/2022	944-188	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
164	10/19/2022	944-189	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
165	10/20/2022	944-215	General Ledger		SALT & PEPPER, INC-364899871	$49.99				0000
166	10/21/2022	944-233	General Ledger		STAPLES DIRECT	$44.51				0000
167	10/25/2022	944-284	General Ledger		STAPLES DIRECT	$287.09				0000
168	10/28/2022	904-30	Accounts Payable		SALT & PEPPER, INC-364569916	$125.00				0000
169	10/28/2022	904-32	Accounts Payable		SALT & PEPPER, INC-364582722	$64.00				0000
170	10/28/2022	904-34	Accounts Payable		SALT & PEPPER, INC-364645487	$58.99				0000
171	11/09/2022	911-65	Accounts Payable		KITT'S MUSIC-603739232	$4,205.00				0000
172	11/09/2022	911-101	Accounts Payable		SALT & PEPPER, INC-364683994	$286.99				0000
173	11/09/2022	911-103	Accounts Payable		SALT & PEPPER, INC-364691138	$56.00				0000
174	11/30/2022	1015-62	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232		$4,205.00			0000
175
176
177	03/24/2023 10:33:32 AM								Page 4
XTRA

 

[mumps]

What about row 182 in your previous data posted and any other rows that may be there below?

 

[NewFrugal]

What about row 182 in your previous data posted and any other rows that may be there below?

Yes, I cleaned row 182 up as well as the other rows beneath it.

 

[mumps]

Sorry, but I don't see row 182 in the data you posted in Post #14.

 

[NewFrugal]

Sorry about that. Here ya go:

MrExcel Detail Report Sample.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Hogwarts School
2	2022-23 Department Detail Report
3
4	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
5	100 - Classics
6	Account:	01-6200-100 (Resource Supplies-Classics)
7	07/01/2022	Account Beginning Balance							$0.00
8	02/27/2023	1023-327	Accounts Payable		DOBBY-none-022323	$940.95				0000
9						$940.95	$0.00
10	02/28/2023	Account Net Change							$940.95
11	02/28/2023	Account Ending Balance							$940.95
12	Account:	01-7045-100 (Office Supplies-Classics)
13	07/01/2022	Account Beginning Balance							$0.00
14	12/08/2022	0-28	Cash Receipts		Unposted Cash Management Adjustment		$10.00			0000
15	12/15/2022	1023-359	Accounts Payable		BLACKBELT-	$10.00				0000
16						$10.00	$10.00
17	02/28/2023	Account Net Change							$0.00
18	02/28/2023	Account Ending Balance							$0.00
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43	03/24/2023 10:33:32 AM								Page 1
44
45	Hogwarts School
46	2022-23 Department Detail Report
47
48	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
49	102 - English
50	Account:	01-6200-102 (Resource Supplies-English)
51	07/01/2022	Account Beginning Balance							$0.00
52	11/03/2022	909-69	Accounts Payable		HERMIONE GRANGER-none-101822	$199.95				0000
53	11/09/2022	911-95	Accounts Payable		HERMIONE GRANGER-none-110822	$35.44				0000
54	11/22/2022	931-40	Accounts Payable		HERMIONE GRANGER-3257-111822	$123.48				0000
55	12/08/2022	931-274	Accounts Payable		LUNA LOVEGOOD-3257-120122	$294.44				0000
56	12/15/2022	943-184	Accounts Payable		HERMIONE GRANGER-3257-121222	$56.32				0000
57	12/22/2022	951-42	Accounts Payable		RON WEASLEY-REIMB 121222	$73.24				0000
58	01/11/2023	961-187	Accounts Payable		HERMIONE GRANGER-none-010423	$29.00				0000
59	01/11/2023	961-249	Accounts Payable		ALBUS DUMBLEDORE-3257-010923	$31.25				0000
60	01/11/2023	961-251	Accounts Payable		HARRY POTTER-none-010623	$60.98				0000
61						$904.10	$0.00
62	02/28/2023	Account Net Change							$904.10
63	02/28/2023	Account Ending Balance							$904.10
64	Account:	01-7045-102 (Office Supplies-English)
65	07/01/2022	Account Beginning Balance							$0.00
66	08/29/2022	829-134	Accounts Payable		HERMIONE GRANGER-REIMBURSEMENT 083022	$337.89				0000
67	08/31/2022	889-8	General Ledger		TARGET.COM	$84.85				0000
68						$422.74	$0.00
69	02/28/2023	Account Net Change							$422.74
70	02/28/2023	Account Ending Balance							$422.74
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87	03/24/2023 10:33:32 AM								Page 2
88
89	Hogwarts School
90	2022-23 Department Detail Report
91
92	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
93	103 - Fine Arts
94	Account:	01-6215-103 (Art Supplies-Fine Arts)
95	07/01/2022	Account Beginning Balance							$0.00
96	10/28/2022	904-40	Accounts Payable		RON WEASLEY-3257-102122	$150.38				0000
97	11/09/2022	911-105	Accounts Payable		RON WEASLEY-102122	$168.71				0000
98	01/19/2023	984-174	Accounts Payable		JAMES POTTER-REIM 011923	$2,883.27				0000
99						$3,202.36	$0.00
100	02/28/2023	Account Net Change							$3,202.36
101	02/28/2023	Account Ending Balance							$3,202.36
102	Account:	01-6220-103 (Music Supplies-Fine Arts)
103	07/01/2022	Account Beginning Balance							$0.00
104	07/24/2022	770-33	Accounts Payable		Pitch Perfect Tuning-PIANOLOCKS 081722	$648.00				CLUBS1012
105	11/30/2022	1015-61	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232	$4,205.00				0000
106						$4,853.00	$0.00
107	02/28/2023	Account Net Change							$4,853.00
108	02/28/2023	Account Ending Balance							$4,853.00
109	Account:	01-6225-103 (Band-Fine Arts)
110	07/01/2022	Account Beginning Balance							$0.00
111	09/13/2022	1005-1	Accounts Payable		Newt Scamander-	$79.50				0000
112	09/15/2022	851-74	Accounts Payable		Newt Scamander-3257-091322	$79.50				0000
113	09/16/2022	859-55	Accounts Payable		SALT & PEPPER, INC-364460625	$77.99				0000
114	09/16/2022	859-71	Accounts Payable		SALT & PEPPER, INC-364464325	$67.00				0000
115	09/16/2022	859-81	Accounts Payable		SALT & PEPPER, INC-364485626	$115.00				0000
116	09/16/2022	859-97	Accounts Payable		SALT & PEPPER, INC-364502224	$45.99				0000
117	09/16/2022	859-201	Accounts Payable		SALT & PEPPER, INC-364383278	$57.99				0000
118	09/16/2022	859-203	Accounts Payable		SALT & PEPPER, INC-364400557	$40.00				0000
119	09/16/2022	859-205	Accounts Payable		SALT & PEPPER, INC-364381861	$13.00				0000
120	09/16/2022	859-207	Accounts Payable		SALT & PEPPER, INC-364074730	$196.99				0000
121	09/16/2022	859-209	Accounts Payable		SALT & PEPPER, INC-364184267	$4.99				0000
122	09/16/2022	859-211	Accounts Payable		SALT & PEPPER, INC-364262591	$45.00				0000
123	09/16/2022	859-213	Accounts Payable		SALT & PEPPER, INC-364185501	$152.99				0000
124	09/16/2022	859-215	Accounts Payable		SALT & PEPPER, INC-364380865	$124.99				0000
125	09/16/2022	859-217	Accounts Payable		SALT & PEPPER, INC-364184783	$2.99				0000
126	09/16/2022	859-219	Accounts Payable		SALT & PEPPER, INC-364171249	$60.00				0000
127	09/16/2022	859-221	Accounts Payable		SALT & PEPPER, INC-364049735	$49.95				0000
128	09/16/2022	859-223	Accounts Payable		SALT & PEPPER, INC-364015100	$130.00				0000
129	09/16/2022	859-225	Accounts Payable		SALT & PEPPER, INC-364045885	$121.99				0000
130
131	03/24/2023 10:33:32 AM								Page 3
132
133	Hogwarts School
134	2022-23 Department Detail Report
135
136	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
137	103 - Fine Arts
138	Account:	01-6225-103 (Band-Fine Arts)
139	09/21/2022	889-219	General Ledger		MUSIC ON THE HILL	$121.87				0000
140	09/26/2022	889-331	General Ledger		VENMO	$309.00				0000
141	10/22/2022	944-247	General Ledger		FOXES MUSIC CO	$31.81				0000
142	10/25/2022	944-276	General Ledger		WM SUPERCENTER #5941	$6.96				0000
143	10/25/2022	944-277	General Ledger		UNOCHICAGOGRILL#701	$562.39				0000
144	11/09/2022	911-93	Accounts Payable		SALT & PEPPER, INC-364744103	$45.00				0000
145	12/21/2022	941-134	General Ledger		AvidPay credit (ck #95054, A. Wood)		$79.50			0000
146						$2,542.89	$79.50
147	02/28/2023	Account Net Change							$2,463.39
148	02/28/2023	Account Ending Balance							$2,463.39
149	Account:	01-6230-103 (Choral Arts-Fine Arts)
150	07/01/2022	Account Beginning Balance							$0.00
151	07/29/2022	770-86	Accounts Payable		SALT & PEPPER, INC-364280581	$202.82				0000
152	07/29/2022	775-329	General Ledger		SALT & PEPPER, INC-364280581		$202.82			0000
153	08/09/2022	773-145	Accounts Payable		SALT & PEPPER, INC-364353241	$35.98				0000
154	08/09/2022	775-342	General Ledger		SALT & PEPPER, INC-364353241		$35.98			0000
155	09/14/2022	889-158	General Ledger		SALT & PEPPER, INC-364899871	$262.94				0000
156	09/15/2022	851-60	Accounts Payable		Ginny Weasley-3257-083122	$401.17				0000
157	09/15/2022	851-80	Accounts Payable		Ginny Weasley-3257-091322	$193.84				0000
158	09/15/2022	889-170	General Ledger		SALT & PEPPER, INC-364899871	$50.70				0000
159	09/19/2022	889-197	General Ledger		WWW.ACDA.ORG	$125.00				0000
160	09/21/2022	889-224	General Ledger		GIA PUBLICATIONS INC	$64.59				0000
161	10/13/2022	944-125	General Ledger		SQ SWEET SPOT STUD	$385.00				0000
162	10/18/2022	944-178	General Ledger		SALT & PEPPER, INC-364899871	$5,318.87				0000
163	10/19/2022	944-188	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
164	10/19/2022	944-189	General Ledger		SALT & PEPPER, INC-364899871	$9.50				0000
165	10/20/2022	944-215	General Ledger		SALT & PEPPER, INC-364899871	$49.99				0000
166	10/21/2022	944-233	General Ledger		STAPLES DIRECT	$44.51				0000
167	10/25/2022	944-284	General Ledger		STAPLES DIRECT	$287.09				0000
168	10/28/2022	904-30	Accounts Payable		SALT & PEPPER, INC-364569916	$125.00				0000
169	10/28/2022	904-32	Accounts Payable		SALT & PEPPER, INC-364582722	$64.00				0000
170	10/28/2022	904-34	Accounts Payable		SALT & PEPPER, INC-364645487	$58.99				0000
171	11/09/2022	911-65	Accounts Payable		KITT'S MUSIC-603739232	$4,205.00				0000
172	11/09/2022	911-101	Accounts Payable		SALT & PEPPER, INC-364683994	$286.99				0000
173	11/09/2022	911-103	Accounts Payable		SALT & PEPPER, INC-364691138	$56.00				0000
174	11/30/2022	1015-62	General Ledger		Reclassify 911-62: Kitt's Music INV #603739232		$4,205.00			0000
175
176
177	03/24/2023 10:33:32 AM								Page 4
178
179	Hogwarts School
180	2022-23 Department Detail Report
181
182	Date	Trans.	Journal		Reference	Debit Amount	Credit Amount		Balance	Project ID
183	103 - Fine Arts
184	Account:	01-6230-103 (Choral Arts-Fine Arts)
185	01/10/2023	1059-18	Accounts Payable		SALT & PEPPER, INC-	$166.97				0000
186	01/12/2023	961-269	Accounts Payable		Ginny Weasley-none-010322	$90.00				0000
187	01/12/2023	961-271	Accounts Payable		SALT & PEPPER, INC-364899871	$122.99				0000
188	01/12/2023	961-273	Accounts Payable		SALT & PEPPER, INC-364902908	$45.00				0000
189	01/18/2023	984-102	Accounts Payable		SALT & PEPPER, INC-364941825	$40.94				0000
190	01/19/2023	999-61	Cash Receipts		Choir-823-1418		$520.45			0000
191	01/31/2023	1005-25	Accounts Payable		FRANCESCA HURST-25	$350.00				0000
192	02/27/2023	1023-321	Accounts Payable		SALT & PEPPER, INC-365019499	$71.50				0000
193	02/27/2023	1023-323	Accounts Payable		SALT & PEPPER, INC-364980015	$227.56				0000
194	02/27/2023	1023-325	Accounts Payable		SALT & PEPPER, INC-365102855	$49.99				0000
195						$13,402.43	$4,964.25
196	02/28/2023	Account Net Change							$8,438.18
197	02/28/2023	Account Ending Balance							$8,438.18
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223	03/24/2023 10:33:32 AM								Page 5
XTRA

 

[mumps]

I noticed that some of the department names in you your data have duplicate values (A93, A137, A183). Are duplicate department names possible? If so, that will pose a problem when new sheets are created because Excel does not allow tow or more sheets to have the same name.

 

[NewFrugal]

I noticed that some of the department names in you your data have duplicate values (A93, A137, A183). Are duplicate department names possible? If so, that will pose a problem when new sheets are created because Excel does not allow tow or more sheets to have the same name.

Yes, that's correct. It's because those duplicate values represent a continuation of department data. When these reports are downloaded, they're downloaded as one report with multiple pages (similar to a PDF). The only solution I can readily think of is if I go in and remove the repeat header lines that begin with Hogwarts School and the two or three lines beneath it for those "pages" that are simply a continuation of data.