Match/Lookup separate tables into one column

[Ashleytaylor1702]

Hi All,

I am in the process of creating a budget planner and have two separate ingoing/outgoing tables in sheet 1.

The first table has all expenses that are debited/credited on a set day of the month (For example 1st,4th,18th)

The second table has all expenses that are debited/credited on a set day of the week (For example Monday, or Wednesday)

What I require is a formula in sheet 2 in columns B & C that lookups up the expenses and amounts from sheet 1 and transfers them across to the relevant day of the month or day of the week
and repeats the pattern through the months in sheet 2.

Please see attached example workbook - Note that I have manually pasted my requirement and changed the font colour to red for clarity.

Many Thanks

Sheet 1

Test Planner V1.xlsx
	A	B	C	D	E	F	G
1	Date of month	Ingoing/Outgoing	Amount		Day of week	Ingoing/Outgoing	Amount
2	1	Income 1	-100		Friday	Food Shopping	10
3	1	Mortgage	25
4	1
5	1
6	1
7	1
8	1
9	1
10	2	Gas	15
11	2
12	2
13	2
14	2
15	2
16	2
17	2
18	3	Electric	10
19	3	Internet	12
20	3	House Insurance	8
21	3
22	3
23	3
24	3
25	3
26	4
27	4
28	4
29	4
30	4
31	4
32	4
33	4
34	5	Car Insurance	4
35	5
36	5
37	5
38	5
39	5
40	5
41	5
42	6
43	6
44	6
45	6
46	6
47	6
48	6
49	6
50	7
51	7
52	7
53	7
54	7
55	7
56	7
57	7
58	8
59	8
60	8
61	8
62	8
63	8
64	8
65	8
66	9
67	9
68	9
69	9
70	9
71	9
72	9
73	9
74	10
75	10
76	10
77	10
78	10
79	10
80	10
81	10
82	11
83	11
84	11
85	11
86	11
87	11
88	11
89	11
90	12
91	12
92	12
93	12
94	12
95	12
96	12
97	12
98	13
99	13
100	13
101	13
102	13
103	13
104	13
105	13
106	14
107	14
108	14
109	14
110	14
111	14
112	14
113	14
114	15	Income 2	-125
115	15
116	15
117	15
118	15
119	15
120	15
121	15
122	16
123	16
124	16
125	16
126	16
127	16
128	16
129	16
130	17
131	17
132	17
133	17
134	17
135	17
136	17
137	17
138	18	Car Payment	10
139	18
140	18
141	18
142	18
143	18
144	18
145	18
146	19
147	19
148	19
149	19
150	19
151	19
152	19
153	19
154	20
155	20
156	20
157	20
158	20
159	20
160	20
161	20
162	21
163	21
164	21
165	21
166	21
167	21
168	21
169	21
170	22	Shopping	10
171	22
172	22
173	22
174	22
175	22
176	22
177	22
178	23
179	23
180	23
181	23
182	23
183	23
184	23
185	23
186	24
187	24
188	24
189	24
190	24
191	24
192	24
193	24
194	25
195	25
196	25
197	25
198	25
199	25
200	25
201	25
202	26
203	26
204	26
205	26
206	26
207	26
208	26
209	26
210	27
211	27
212	27
213	27
214	27
215	27
216	27
217	27
218	28	Water	6
219	28
220	28
221	28
222	28
223	28
224	28
225	28
Sheet1

Sheet 2

Test Planner V1.xlsx
	A	B	C	D
1	Date	Ingoing/Outgoing	Amount	Balance
2				100
3	Saturday 01/04/2023	Income 1	-100	200
4	Saturday 01/04/2023	Mortgage	25	175
5	Saturday 01/04/2023			175
6	Saturday 01/04/2023			175
7	Saturday 01/04/2023			175
8	Saturday 01/04/2023			175
9	Saturday 01/04/2023			175
10	Saturday 01/04/2023			175
11	Sunday 02/04/2023	Gas	15	160
12	Sunday 02/04/2023			160
13	Sunday 02/04/2023			160
14	Sunday 02/04/2023			160
15	Sunday 02/04/2023			160
16	Sunday 02/04/2023			160
17	Sunday 02/04/2023			160
18	Sunday 02/04/2023			160
19	Monday 03/04/2023	Electric	10	150
20	Monday 03/04/2023	Internet	12	138
21	Monday 03/04/2023	House Insurance	8	130
22	Monday 03/04/2023			130
23	Monday 03/04/2023			130
24	Monday 03/04/2023			130
25	Monday 03/04/2023			130
26	Monday 03/04/2023			130
27	Tuesday 04/04/2023			130
28	Tuesday 04/04/2023			130
29	Tuesday 04/04/2023			130
30	Tuesday 04/04/2023			130
31	Tuesday 04/04/2023			130
32	Tuesday 04/04/2023			130
33	Tuesday 04/04/2023			130
34	Tuesday 04/04/2023			130
35	Wednesday 05/04/2023	Car Insurance	4	126
36	Wednesday 05/04/2023			126
37	Wednesday 05/04/2023			126
38	Wednesday 05/04/2023			126
39	Wednesday 05/04/2023			126
40	Wednesday 05/04/2023			126
41	Wednesday 05/04/2023			126
42	Wednesday 05/04/2023			126
43	Thursday 06/04/2023			126
44	Thursday 06/04/2023			126
45	Thursday 06/04/2023			126
46	Thursday 06/04/2023			126
47	Thursday 06/04/2023			126
48	Thursday 06/04/2023			126
49	Thursday 06/04/2023			126
50	Thursday 06/04/2023			126
51	Friday 07/04/2023	Food Shopping	10	116
52	Friday 07/04/2023			116
53	Friday 07/04/2023			116
54	Friday 07/04/2023			116
55	Friday 07/04/2023			116
56	Friday 07/04/2023			116
57	Friday 07/04/2023			116
58	Friday 07/04/2023			116
59	Saturday 08/04/2023			116
60	Saturday 08/04/2023			116
61	Saturday 08/04/2023			116
62	Saturday 08/04/2023			116
63	Saturday 08/04/2023			116
64	Saturday 08/04/2023			116
65	Saturday 08/04/2023			116
66	Saturday 08/04/2023			116
67	Sunday 09/04/2023			116
68	Sunday 09/04/2023			116
69	Sunday 09/04/2023			116
70	Sunday 09/04/2023			116
71	Sunday 09/04/2023			116
72	Sunday 09/04/2023			116
73	Sunday 09/04/2023			116
74	Sunday 09/04/2023			116
75	Monday 10/04/2023			116
76	Monday 10/04/2023			116
77	Monday 10/04/2023			116
78	Monday 10/04/2023			116
79	Monday 10/04/2023			116
80	Monday 10/04/2023			116
81	Monday 10/04/2023			116
82	Monday 10/04/2023			116
83	Tuesday 11/04/2023			116
84	Tuesday 11/04/2023			116
85	Tuesday 11/04/2023			116
86	Tuesday 11/04/2023			116
87	Tuesday 11/04/2023			116
88	Tuesday 11/04/2023			116
89	Tuesday 11/04/2023			116
90	Tuesday 11/04/2023			116
91	Wednesday 12/04/2023			116
92	Wednesday 12/04/2023			116
93	Wednesday 12/04/2023			116
94	Wednesday 12/04/2023			116
95	Wednesday 12/04/2023			116
96	Wednesday 12/04/2023			116
97	Wednesday 12/04/2023			116
98	Wednesday 12/04/2023			116
99	Thursday 13/04/2023			116
100	Thursday 13/04/2023			116
101	Thursday 13/04/2023			116
102	Thursday 13/04/2023			116
103	Thursday 13/04/2023			116
104	Thursday 13/04/2023			116
105	Thursday 13/04/2023			116
106	Thursday 13/04/2023			116
107	Friday 14/04/2023	Food Shopping	10	106
108	Friday 14/04/2023			106
109	Friday 14/04/2023			106
110	Friday 14/04/2023			106
111	Friday 14/04/2023			106
112	Friday 14/04/2023			106
113	Friday 14/04/2023			106
114	Friday 14/04/2023			106
115	Saturday 15/04/2023	Income 2	-125	231
116	Saturday 15/04/2023			231
117	Saturday 15/04/2023			231
118	Saturday 15/04/2023			231
119	Saturday 15/04/2023			231
120	Saturday 15/04/2023			231
121	Saturday 15/04/2023			231
122	Saturday 15/04/2023			231
123	Sunday 16/04/2023			231
124	Sunday 16/04/2023			231
125	Sunday 16/04/2023			231
126	Sunday 16/04/2023			231
127	Sunday 16/04/2023			231
128	Sunday 16/04/2023			231
129	Sunday 16/04/2023			231
130	Sunday 16/04/2023			231
131	Monday 17/04/2023			231
132	Monday 17/04/2023			231
133	Monday 17/04/2023			231
134	Monday 17/04/2023			231
135	Monday 17/04/2023			231
136	Monday 17/04/2023			231
137	Monday 17/04/2023			231
138	Monday 17/04/2023			231
139	Tuesday 18/04/2023	Car Payment	10	221
140	Tuesday 18/04/2023			221
141	Tuesday 18/04/2023			221
142	Tuesday 18/04/2023			221
143	Tuesday 18/04/2023			221
144	Tuesday 18/04/2023			221
145	Tuesday 18/04/2023			221
146	Tuesday 18/04/2023			221
147	Wednesday 19/04/2023			221
148	Wednesday 19/04/2023			221
149	Wednesday 19/04/2023			221
150	Wednesday 19/04/2023			221
151	Wednesday 19/04/2023			221
152	Wednesday 19/04/2023			221
153	Wednesday 19/04/2023			221
154	Wednesday 19/04/2023			221
155	Thursday 20/04/2023			221
156	Thursday 20/04/2023			221
157	Thursday 20/04/2023			221
158	Thursday 20/04/2023			221
159	Thursday 20/04/2023			221
160	Thursday 20/04/2023			221
161	Thursday 20/04/2023			221
162	Thursday 20/04/2023			221
163	Friday 21/04/2023	Food Shopping	10	211
164	Friday 21/04/2023			211
165	Friday 21/04/2023			211
166	Friday 21/04/2023			211
167	Friday 21/04/2023			211
168	Friday 21/04/2023			211
169	Friday 21/04/2023			211
170	Friday 21/04/2023			211
171	Saturday 22/04/2023	Shopping	10	201
172	Saturday 22/04/2023			201
173	Saturday 22/04/2023			201
174	Saturday 22/04/2023			201
175	Saturday 22/04/2023			201
176	Saturday 22/04/2023			201
177	Saturday 22/04/2023			201
178	Saturday 22/04/2023			201
179	Sunday 23/04/2023			201
180	Sunday 23/04/2023			201
181	Sunday 23/04/2023			201
182	Sunday 23/04/2023			201
183	Sunday 23/04/2023			201
184	Sunday 23/04/2023			201
185	Sunday 23/04/2023			201
186	Sunday 23/04/2023			201
187	Monday 24/04/2023			201
188	Monday 24/04/2023			201
189	Monday 24/04/2023			201
190	Monday 24/04/2023			201
191	Monday 24/04/2023			201
192	Monday 24/04/2023			201
193	Monday 24/04/2023			201
194	Monday 24/04/2023			201
195	Tuesday 25/04/2023			201
196	Tuesday 25/04/2023			201
197	Tuesday 25/04/2023			201
198	Tuesday 25/04/2023			201
199	Tuesday 25/04/2023			201
200	Tuesday 25/04/2023			201
201	Tuesday 25/04/2023			201
202	Tuesday 25/04/2023			201
203	Wednesday 26/04/2023			201
204	Wednesday 26/04/2023			201
205	Wednesday 26/04/2023			201
206	Wednesday 26/04/2023			201
207	Wednesday 26/04/2023			201
208	Wednesday 26/04/2023			201
209	Wednesday 26/04/2023			201
210	Wednesday 26/04/2023			201
211	Thursday 27/04/2023			201
212	Thursday 27/04/2023			201
213	Thursday 27/04/2023			201
214	Thursday 27/04/2023			201
215	Thursday 27/04/2023			201
216	Thursday 27/04/2023			201
217	Thursday 27/04/2023			201
218	Thursday 27/04/2023			201
219	Friday 28/04/2023	Water	6	195
220	Friday 28/04/2023	Food Shopping	10	185
221	Friday 28/04/2023			185
222	Friday 28/04/2023			185
223	Friday 28/04/2023			185
224	Friday 28/04/2023			185
225	Friday 28/04/2023			185
226	Friday 28/04/2023			185
227	Saturday 29/04/2023			185
228	Saturday 29/04/2023			185
229	Saturday 29/04/2023			185
230	Saturday 29/04/2023			185
231	Saturday 29/04/2023			185
232	Saturday 29/04/2023			185
233	Saturday 29/04/2023			185
234	Saturday 29/04/2023			185
235	Sunday 30/04/2023			185
236	Sunday 30/04/2023			185
237	Sunday 30/04/2023			185
238	Sunday 30/04/2023			185
239	Sunday 30/04/2023			185
240	Sunday 30/04/2023			185
241	Sunday 30/04/2023			185
242	Sunday 30/04/2023			185
243	Monday 01/05/2023	Income 1	-100	285
244	Monday 01/05/2023	Mortgage	25	260
245	Monday 01/05/2023			260
246	Monday 01/05/2023			260
247	Monday 01/05/2023			260
248	Monday 01/05/2023			260
249	Monday 01/05/2023			260
250	Monday 01/05/2023			260
251	Tuesday 02/05/2023	Gas	15	245
252	Tuesday 02/05/2023			245
Sheet2

Cell Formulas
Range	Formula
D3:D252	D3	=D2-C3

 

[Skybluekid]

Would it be possible tolet us know what version you are using, as this will help with posting solutions

 

[Ashleytaylor1702]

Would it be possible tolet us know what version you are using, as this will help with posting solutions

Yes, no problem. I am using MS 365 Version 2102

Thanks

 

[Skybluekid]

I have created a Power Query solution

Dropbox

www.dropbox.com

 

[Ashleytaylor1702]

I have created a Power Query solution

Dropbox

www.dropbox.com

This is great

I have no experience with Power Query. so not sure how I increase the date range in the query table as I need to be able to see a future forecast of the ingoing/outgoings and balance several months in advance?

Thanks for any help

 

[Fluff]

I have created a Power Query solution

Please post the code code that you used, as per forum rules. Thanks

 

[Skybluekid]

Sorry @Fluff. How do you post code for Power Query?

 

[Fluff]

Just copy it from the editor & post it using the Mcode tags

 

[Skybluekid]

This is the code for the Day of Week Table

= Table.TransformColumnTypes(Source,{{"Date of Month", type date}, {"Incoming/Outgoing", type text}, {"Amount", Int64.Type}})

This is the code for the Day of Month Table

= Table.TransformColumnTypes(Source,{{"Day of Week", type date}, {"Incoming/Outgoing", type text}, {"Amount", Int64.Type}})

This the Code for the Final Table

= Table.Combine({DOM, DOW})
= Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Date of Month", type text}, {"Day of Week", type text}}, "en-GB"),{"Date of Month", "Day of Week"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date")
= Table.ReorderColumns(#"Merged Columns",{"Date", "Incoming/Outgoing", "Amount"})
= Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})

 

[Skybluekid]

@Ashleytaylor1702There is lots of information on Powery Query on You Tube etc. To increase the date range just add the dates into the Days of Week and Month tables. Then when you need to add costs, just fill in the cost column. Once done, go to Data Ribbon, under Queries and Connections choose Refresh All. The Combined Table will then populate.