Tell Me I'm Crazy

[pastorkc]

Is there a way to get the info from the first sheet and the info from the second sheet to merge and look like the info from the third sheet?

GL Q40.xlsx
	A	B	C	D	E	F	G	H
1	Vendor ID	Vendor Name	Account ID	Account Description	Date	Reference	Debit Amt	Credit Amt
2			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS Web Hosting	260.00
3			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS LAN Hosting	277.88
4			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS MGMT	145.38
5			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS Lan Host	443.33
6			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS Web Host	327.25
7			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	TS Mgmt Host	183.33
8			5020.02.Q40.D.G	Professional Services (G&A)	4/30/23	NewWave	39.58
9			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS Lan Host	443.33
10			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS Web Host	327.25
11			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS Mgmt Host	183.33
12			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	NewWave	39.58
13			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS Web Hosting	280.83
14			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS LAN Hosting	300.00
15			5020.02.Q40.D.G	Professional Services (G&A)	5/31/23	TS MGMT	157.08
16	BRANDWISE	Brand Wise	5020.02.Q40.D.G	Professional Services (G&A)	6/7/23	2737	3,765.00
17	ON YOUR MARK	On Your Mark Transportation	5020.02.Q40.D.G	Professional Services (G&A)	6/16/23	061623	3,400.00
18	ON YOUR MARK	On Your Mark Transportation	5020.02.Q40.D.G	Professional Services (G&A)	6/17/23	061723	3,175.85
19			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS Lan Host	443.33
20			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS Web Host	327.25
21			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS Mgmt Host	183.33
22			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	NewWave	39.58
23			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS Web Hosting	280.83
24			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS LAN Hosting	300.00
25			5020.02.Q40.D.G	Professional Services (G&A)	6/30/23	TS MGMT	157.08
26			5020.02.Q40.D.G	Professional Services (G&A)			15,480.40
27	HIGGS	Stanton Higgs	5040.01.Q40.D.G	Utilities (G&A)	4/8/23	040823	45.00
28	HIGGS	Stanton Higgs	5040.01.Q40.D.G	Utilities (G&A)	5/9/23	050923	45.00
29	HIGGS	Stanton Higgs	5040.01.Q40.D.G	Utilities (G&A)	6/1/23	053123	45.00
30	HIGGS	Stanton Higgs	5040.01.Q40.D.G	Utilities (G&A)	6/30/23	060123	45.00
31			5040.01.Q40.D.G	Utilities (G&A)			180.00
32			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	4/30/23	Linked In	55.99
33			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	4/30/23	Crexie	250.00
34	BANK1929 Debbie	Bankcard Center	5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	5/24/23	052423	998.16
35			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	5/31/23	Linked In	55.99
36			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	5/31/23	Crexie	125.00
37			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	6/30/23	Linked In	55.99
38			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)	6/30/23	Crexie	125.00
39			5090.01.Q40.D.G	Dues, Subs, Tuition (G&A)			1,666.13
40			5090.13.Q40.D.G	Print Advertising (G&A)	4/30/23	Journal Communicatio	254.17
41			5090.13.Q40.D.G	Print Advertising (G&A)	5/31/23	Journal Communicatio	254.17
42			5090.13.Q40.D.G	Print Advertising (G&A)	6/30/23	Journal Communicatio	254.17
43			5090.13.Q40.D.G	Print Advertising (G&A)			762.51
44	CBL	Cool Springs Mall, LLC	5225.01.Q40.D.G	Parking Leases	4/1/23	040123	750.00
45	CBL	Cool Springs Mall, LLC	5225.01.Q40.D.G	Parking Leases	5/1/23	050123	750.00
46	CBL	Cool Springs Mall, LLC	5225.01.Q40.D.G	Parking Leases	6/1/23	060123	750.00
47			5225.01.Q40.D.G	Parking Leases			2,250.00
48			9920.99.Q40.D.G	Indirect Cost Recovered	4/30/23	Indirect Expenditure	1,854.42
49			9920.99.Q40.D.G	Indirect Cost Recovered	5/31/23	Indirect Expenditure	2,599.99
50			9920.99.Q40.D.G	Indirect Cost Recovered	6/30/23	Indirect Expenditure	2,843.75
51			9920.99.Q40.D.G	Indirect Cost Recovered			7,298.16
GL Q40-Macro

GL Q40.xlsx
	A	B	C	D	E	F	G
1	Vendor ID	Vendor Name	Invoice No.	Transaction	Trans No.	Date	Amount
2	12-POINT SIGN WORKS	12-Point Sign Works	INV-24828	Invoice	INV-24828	6/30/23	1,455.37
3	12-POINT SIGN WORKS	12-Point Sign Works		Payment	21763	7/28/23	-1,455.37
4
5							0.00
6
7	12-POINT SIGN WORKS						0.00
8
9	AIR	Air Conditioning Service, Inc.	i10001513	Invoice	i10001513	5/24/23	175.00
10	AIR	Air Conditioning Service, Inc.		Payment	21584	6/1/23	-175.00
11
12							0.00
13
14	AIR						0.00
15
16	ALL	All Pest Solutions, Inc.	83980	Invoice	83980	4/4/23	300.00
17	ALL	All Pest Solutions, Inc.		Payment	21564	5/16/23	-300.00
18
19							0.00
20			85302	Invoice	85302	5/4/23	85.00
21				Payment	21536	5/8/23	-85.00
22
23							0.00
24			87204	Invoice	87204	6/16/23	85.00
25				Payment	21675	6/20/23	-85.00
26
27							0.00
28
29	ALL						0.00
30
31	AMPLIFY	Amplify Awards & Gifting	48334	Invoice	48334	6/7/23	60.75
32	AMPLIFY	Amplify Awards & Gifting		Payment	21649	6/8/23	-60.75
33
34							0.00
35
36	AMPLIFY						0.00
37
38	ATMOS-41328	Atmos Energy	041323	Invoice	041323	4/13/23	212.48
39	ATMOS-41328	Atmos Energy		Payment	21502	4/18/23	-212.48
40
41							0.00
42			051123	Invoice	051123	5/11/23	54.25
43				Payment	21565	5/16/23	-54.25
44
45							0.00
46			061323	Invoice	061323	6/13/23	47.72
47				Payment	21695	6/29/23	-47.72
48
49							0.00
50				Payment	21742	7/18/23	-28.36
51			063023	Invoice	063023	6/30/23	28.36
52
53							0.00
54
55	ATMOS-41328						0.00
56
57	ATMOS-41542	Atmos Energy	041323	Invoice	041323	4/13/23	82.36
58	ATMOS-41542	Atmos Energy		Payment	21503	4/18/23	-82.36
59
60							0.00
61			051123	Invoice	051123	5/11/23	47.82
62				Payment	21575	5/22/23	-47.82
63
64							0.00
65			061323	Invoice	061323	6/13/23	42.45
66				Payment	21696	6/29/23	-42.45
67
68							0.00
69				Payment	21743	7/18/23	-25.26
70			063023	Invoice	063023	6/30/23	25.26
71
72							0.00
73
74	ATMOS-41542						0.00
75
76	Auto-Owners	Auto-Owners Insurance		Payment	ACH 070123	7/7/23	981.00
77	Auto-Owners	Auto-Owners Insurance	ROP 063023	Invoice	ROP 063023	6/30/23	-981.00
78
79							0.00
80
81	Auto-Owners						0.00
82
83	AWSI	Advanced Workplace Strategies, Inc	569655	Invoice	569655	4/28/23	54.53
84	AWSI	Advanced Workplace Strategies, Inc		Payment	21526	5/2/23	-54.53
85
86							0.00
87			574009	Invoice	574009	5/31/23	167.34
88				Payment	21653	6/12/23	-167.34
89
90							0.00
91			581185	Invoice	581185	6/30/23	266.59
92				Payment	21723	7/11/23	-266.59
93
94							0.00
95
96	AWSI						0.00
97
98	BAIR	Kelly Bair	041623	Invoice	041623	4/16/23	90.00
99	BAIR	Kelly Bair		Payment	21506	4/19/23	-90.00
100
101							0.00
102			051623	Invoice	051623	5/16/23	90.00
103				Payment	21576	5/22/23	-90.00
104
105							0.00
106			061623	Invoice	061623	6/16/23	90.00
107				Payment	21676	6/20/23	-90.00
108
109							0.00
110			062823	Invoice	062823	6/28/23	46.52
111				Payment	21697	6/29/23	-46.52
112
113							0.00
114			063023	Invoice	063023	6/30/23	42.00
115				Payment	21755	7/19/23	-42.00
116
117							0.00
118			Bonus 060123	Invoice	Bonus 060123	6/1/23	4,160.00
119				Payment	21590	6/1/23	-4,160.00
120
121							0.00
122			Triennial Bonus	Invoice	Triennial Bonus	6/20/23	1,000.00
123				Payment	21679	6/20/23	-1,000.00
124
125							0.00
126			Wellness Bonus	Invoice	Wellness Bonus	4/12/23	500.00
127				Payment	21472	4/12/23	-500.00
128
129							0.00
130
131	BAIR						0.00
132
133	BAKER	Arvin Baker	Bonus 060123	Invoice	Bonus 060123	6/1/23	250.00
134	BAKER	Arvin Baker		Payment	21591	6/1/23	-250.00
135
136							0.00
137
138	BAKER						0.00
139
140	BANK1929 Debbie	Bankcard Center	042423	Invoice	042423	4/24/23	3,175.30
141	BANK1929 Debbie	Bankcard Center		Payment	21554	5/10/23	-3,175.30
142
143							0.00
144			052423	Invoice	052423	5/24/23	6,240.45
145				Payment	0854	6/7/23	-6,240.45
146				Payment	0854V	6/7/23	6,240.45
147				Payment	0857	6/7/23	-998.16
148				Payment	21648	6/7/23	-5,242.29
149
150							0.00
151			062423	Invoice	062423	6/24/23	3,257.89
152				Payment	21740	7/18/23	-3,257.89
153
154							0.00
155
156	BANK1929 Debbie						0.00
157
158	BANK5204 - Monte	BankCard Center	042423	Invoice	042423	4/24/23	54.61
159	BANK5204 - Monte	BankCard Center		Payment	21527	5/2/23	-54.61
160
161							0.00
162			062423	Invoice	062423	6/24/23	55.99
163				Payment	21703	7/3/23	-55.99
164
165							0.00
166			072423	Invoice	072423	6/28/23	580.58
167				Payment	21782	8/2/23	-580.58
168
169							0.00
170
171	BANK5204 - Monte						0.00
172
173	BANK5508 Higgs	BankCard Center	040123	Invoice	040123	4/1/23	684.34
174	BANK5508 Higgs	BankCard Center		Payment	21562	5/29/23	-684.34
175
176							0.00
177			040123-1	Invoice	040123-1	4/1/23	1,500.00
178				Payment	0847	5/15/23	-1,500.00
179
180							0.00
181			040123-2	Invoice	040123-2	4/1/23	75.38
182				Payment	6968	5/15/23	-75.38
183
184							0.00
185			042423	Invoice	042423	4/24/23	1,488.00
186				Payment	0847	5/15/23	-1,488.00
187
188							0.00
189			042423-1	Invoice	042423-1	4/24/23	391.48
190				Payment	21562	5/29/23	-391.48
191
192							0.00
193			052423-01	Invoice	052423-01	5/24/23	1,588.98
194				Payment	21685	6/22/23	-1,588.98
195
196							0.00
197			062423	Invoice	062423	6/24/23	1,093.67
198				Payment	21741	7/18/23	-645.94
199				Payment	0872	7/18/23	-447.73
200
201							0.00
202
203	BANK5508 Higgs						0.00
204
205	BANK6676 Stephanie	BankCard Center	062423	Invoice	062423	6/24/23	60.14
206	BANK6676 Stephanie	BankCard Center		Payment	0871	7/17/23	-60.14
207
208							0.00
209			063023	Invoice	063023	6/30/23	974.50
210				Payment	0892	8/7/23	-974.50
211
212							0.00
213
214	BANK6676 Stephanie						0.00
215
216	BEAMAN TOYOTA	Beaman Toyota	121085	Invoice	121085	6/8/23	628.47
217	BEAMAN TOYOTA	Beaman Toyota		Payment	6998	6/12/23	-628.47
218
219							0.00
220
221	BEAMAN TOYOTA						0.00
222
223	BEARDENB	Bonnie Bearden	Bonus 060123	Invoice	Bonus 060123	6/1/23	250.00
224	BEARDENB	Bonnie Bearden		Payment	21592	6/1/23	-250.00
225	BEARDENB	Bonnie Bearden		Payment	21592V	6/13/23	250.00
226	BEARDENB	Bonnie Bearden		Payment	21669	6/13/23	-250.00
227
228							0.00
229
230	BEARDENB						0.00
231
232	BRANDWISE	Brand Wise	2688	Invoice	2688	4/6/23	4,338.02
233	BRANDWISE	Brand Wise		Payment	21537	5/8/23	-4,338.02
234
235							0.00
236			2689	Invoice	2689	4/6/23	4,167.77
237				Payment	0844	5/8/23	-4,167.77
238
239							0.00
240			2690	Invoice	2690	4/6/23	2,395.00
241				Payment	0844	5/8/23	-2,395.00
242
243							0.00
244			2733	Invoice	2733	6/7/23	2,642.71
245				Payment	0877	7/21/23	-2,642.71
246
247							0.00
248			2734	Invoice	2734	6/7/23	2,000.00
249				Payment	0877	7/21/23	-2,000.00
250
251							0.00
252			2735	Invoice	2735	6/7/23	2,000.00
253				Payment	0876	7/21/23	-2,000.00
254
255							0.00
256			2736	Invoice	2736	6/7/23	5,000.00
257				Payment	0876	7/21/23	-5,000.00
258
259							0.00
260			2737	Invoice	2737	6/7/23	3,765.00
261				Payment	0878	7/21/23	-3,765.00
262
263							0.00
264			2749	Invoice	2749	6/30/23	2,758.88
265				Payment	0879	7/21/23	-2,758.88
266
267							0.00
268
269	BRANDWISE						0.00
270
271	BROOKS	Raymond Brooks	Bonus 060123	Invoice	Bonus 060123	6/1/23	2,500.00
272	BROOKS	Raymond Brooks		Payment	21593	6/1/23	-2,500.00
273
274							0.00
275			Wellness Bonus	Invoice	Wellness Bonus	4/12/23	500.00
276				Payment	21473	4/12/23	-500.00
277
278							0.00
279
280	BROOKS						0.00
281
282	CAMPBELL	Barton Campbell	Bonus 060123	Invoice	Bonus 060123	6/1/23	500.00
283	CAMPBELL	Barton Campbell		Payment	21594	6/1/23	-500.00
284
285							0.00
286			Wellness Bonus	Invoice	Wellness Bonus	4/12/23	500.00
287				Payment	21474	4/12/23	-500.00
288
289							0.00
290
291	CAMPBELL						0.00
292
293	CAREER DRIVER	Career Driver, Inc	1127	Invoice	1127	4/10/23	500.00
294	CAREER DRIVER	Career Driver, Inc		Payment	21468	4/11/23	-500.00
295
296							0.00
297			1157	Invoice	1157	5/16/23	500.00
298				Payment	21633	6/6/23	-500.00
299
300							0.00
301
302	CAREER DRIVER						0.00
303
304	CARPENTER	Carpenter Bus Sales	131707	Invoice	131707	5/11/23	140.00
305	CARPENTER	Carpenter Bus Sales		Payment	21566	5/16/23	-140.00
306
307							0.00
308			131717	Invoice	131717	5/15/23	140.00
309				Payment	21566	5/16/23	-140.00
310
311							0.00
312			131901	Invoice	131901	4/28/23	22,794.72
313				Payment	21528	5/2/23	-22,794.72
314
315							0.00
316			132017	Invoice	132017	4/6/23	3,820.23
317				Payment	21462	4/10/23	-3,820.23
318
319							0.00
320			132036	Invoice	132036	4/10/23	2,161.50
321				Payment	21493	4/12/23	-2,161.50
322
323							0.00
324			132066	Invoice	132066	4/11/23	140.00
325				Payment	21493	4/12/23	-140.00
326
327							0.00
328			132089	Invoice	132089	4/17/23	1,064.28
329				Payment	21507	4/19/23	-1,064.28
330
331							0.00
332			132107	Invoice	132107	4/20/23	995.00
333				Payment	21516	4/25/23	-995.00
334
335							0.00
336			132117	Invoice	132117	4/17/23	197.50
337				Payment	21507	4/19/23	-197.50
338
339							0.00
340			132123	Invoice	132123	4/19/23	1,296.43
341				Payment	21511	4/24/23	-1,296.43
342
343							0.00
344			132147	Invoice	132147	4/26/23	1,777.67
345				Payment	21519	4/27/23	-1,777.67
346
347							0.00
348			132181	Invoice	132181	4/28/23	3,441.13
349				Payment	21528	5/2/23	-3,441.13
350
Vendor History

GL Q40.xlsx
	A	B	C	D	E	F	G
1	Local Government Guidelines Form 9-3
2	March 15, 2019
3
4	DESIGN INVOICE SUMMARY
5	This is required with every invoice submittal.
6
7		Invoice # (Numbered Chronologically)	Billing Amount	Amount Requested for Reimbursment	Check/Wire #
8				100.0%
9
10		24	300.00	300.00	0798
11		25	800.00	800.00	0802
12		26	1,500.00	750.00	0833
13		2224	475.00	39.58	0660
14		2224	475.00	39.58	0660
15		17210	3,050.00	254.17	0756
16		17210	3,050.00	254.17	0756
17		20123	750.00	750.00	0801
18		30123	400.00	400.00	0811
19		32223	52.67	52.67	0824
20		121087	1,500.00	125.00	20647
21		5322137159	125.79	125.79	0809
22		8541669034	1,130.09	55.99	6850
23		8541669034	1,130.09	55.99	6850
24		Monthly Rent-2012394	400.00	400.00	0797
25		Monthly Rent-2012394	400.00	400.00	0812
26		TSMAU220148	3,335.00	277.92	20851
27		TSMAU220148	3,335.00	277.92	20851
28		TSMAU220149	4,865.00	260.00	20852
29		TSMAU220149	4,865.00	260.00	20852
30		TSMAU220150	4,865.00	145.42	20852
31		TSMAU220150	4,865.00	145.42	20852
32		TSMAU220759	5,320.00	443.33	21289
33		TSMAU220759	5,320.00	443.33	21289
34		TSMAU220760	3,927.00	327.25	21290
35		TSMAU220760	3,927.00	327.25	21290
36		TSMAU220761	2,200.00	183.33	21288
37		TSMAU220761	2,200.00	183.33	21288
38
39		Indirects	4,804.28	4,804.28
40
41		#####	$ -	$ -
42		Total:	$ 69,366.92	$ 12,881.72
43
44
45		Additional Notes:
46
47
48
49
50
51
52
53
54
Invoice

Cell Formulas
Range	Formula
A2	A2	='T:\Finances\Grant Charts\Wmsn Cty-TMA Regional Rideshare Park n Ride (Q40)\[Q40 - 9-3_Local_Government_Invoice_For_Design- 180143-15.xlsx]Invoice'!A2
D8	D8	=1-'T:\Finances\Grant Charts\Wmsn Cty-TMA Regional Rideshare Park n Ride (Q40)\[Q40 - 9-3_Local_Government_Invoice_For_Design- 180143-15.xlsx]Invoice'!E23
D41	D41	=C41*$D$8
C42:D42	C42	=SUM(C9:C41)

 

[dreid1011]

Sure, it's quite possible, however, your first two sheets don't seem to have anything in common to match up together to produce the desired results on your third sheet. At least, I cannot figure out how to merge the data based on what you are showing. Pretend I know nothing about your data. How should the data on sheet 1 match with sheet 2 and exactly which fields are displayed on sheet 3?

 

[duggie33]

You are crazy.

I was having the same issue as dreid1011 when attempting to decipher it. On top of that, there are links to other files in some cells. The data needs some cleaning to make it usable.

Doug