get row by month them if one of two has data

[Vince549]

I have a large data set a sample of which is below. I need to first put each month from column A on a different tab. Then only show the rows where there is data in column C or E. The person requesting does not want a pivot table and with almost a million rows of data I can't do it manually. Any ideas or direction would be greatly appreciated. Thank you for any help.

 

[JamesCanale]

I can't say how this will perform with a million lines of data. And if you are looking for something that will make the different sheets for the new months, that will take a VBA. But if you can make the new month sheets when you need them, this could be helpful. It's more convoluted that it might need to be - but I'm assuming the date in column a might not always be the first of the month.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Month/Year	InvoiceID	NotPaid	Paid	PaymentDue	Voided		New Sheeet for November 2020							New Sheet for December 2020
2	Mar-21	20600			33
3	Nov-20	20601		100				Nov-20							Dec-20
4	Nov-20	20602		100				Nov-20	20610	50	0	0	0		Dec-20	20628	140	0	0	0
5	Nov-20	20603		110				Nov-20	20621	0	0	0	280		Dec-20	20631	0	0	0	140
6	Nov-20	20604		120
7	Nov-20	20605		130
8	Nov-20	20606		140
9	Nov-20	20607		150
10	Nov-20	20608		160
11	Nov-20	20609		170
12	Nov-20	20610	50
13	Nov-20	20611		190
14	Nov-20	20612		200
15	Nov-20	20613		210
16	Nov-20	20614		220
17	Nov-20	20615		230
18	Nov-20	20616		240
19	Nov-20	20617		250
20	Nov-20	20618		260
21	Nov-20	20619		270
22	Nov-20	20620		280
23	Nov-20	20621				280
24	Nov-20	20622		280
25	Nov-20	20623		280
26	Nov-20	20624		280
27	Nov-20	20625		280
28	Nov-20	20626		280
29	Dec-20	20627		280
30	Dec-20	20628	140
31	Dec-20	20629		140
32	Dec-20	20630		140
33	Dec-20	20631				140
34	Dec-20	20632		140
35	Dec-20	20633		140
36	Dec-20	20634		140
37	Dec-20	20635		140
38	Dec-20	20636		140
39	Jan-21	20637		140
40	Jan-21	20638		140
41	Jan-21	20639		140
42	Jan-21	20640		140
43	Jan-21	20641	150
44	Jan-21	20642		140
45	Jan-21	20643		140
Sheet6

Cell Formulas
Range	Formula
H4:M5	H4	=FILTER(A2:F45,(MONTH(A2:A45)=MONTH(H3))*(YEAR(A2:A45)=YEAR(H3))*((C2:C45<>"")+(F2:F45<>"")),"")
O4:T5	O4	=FILTER(A2:F45,(MONTH(A2:A45)=MONTH(O3))*(YEAR(A2:A45)=YEAR(O3))*((C2:C45<>"")+(F2:F45<>"")),"")
Dynamic array formulas.

 

[Vince549]

I can't say how this will perform with a million lines of data. And if you are looking for something that will make the different sheets for the new months, that will take a VBA. But if you can make the new month sheets when you need them, this could be helpful. It's more convoluted that it might need to be - but I'm assuming the date in column a might not always be the first of the month.

MrExcelPlayground6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Month/Year	InvoiceID	NotPaid	Paid	PaymentDue	Voided		New Sheeet for November 2020							New Sheet for December 2020
2	Mar-21	20600			33
3	Nov-20	20601		100				Nov-20							Dec-20
4	Nov-20	20602		100				Nov-20	20610	50	0	0	0		Dec-20	20628	140	0	0	0
5	Nov-20	20603		110				Nov-20	20621	0	0	0	280		Dec-20	20631	0	0	0	140
6	Nov-20	20604		120
7	Nov-20	20605		130
8	Nov-20	20606		140
9	Nov-20	20607		150
10	Nov-20	20608		160
11	Nov-20	20609		170
12	Nov-20	20610	50
13	Nov-20	20611		190
14	Nov-20	20612		200
15	Nov-20	20613		210
16	Nov-20	20614		220
17	Nov-20	20615		230
18	Nov-20	20616		240
19	Nov-20	20617		250
20	Nov-20	20618		260
21	Nov-20	20619		270
22	Nov-20	20620		280
23	Nov-20	20621				280
24	Nov-20	20622		280
25	Nov-20	20623		280
26	Nov-20	20624		280
27	Nov-20	20625		280
28	Nov-20	20626		280
29	Dec-20	20627		280
30	Dec-20	20628	140
31	Dec-20	20629		140
32	Dec-20	20630		140
33	Dec-20	20631				140
34	Dec-20	20632		140
35	Dec-20	20633		140
36	Dec-20	20634		140
37	Dec-20	20635		140
38	Dec-20	20636		140
39	Jan-21	20637		140
40	Jan-21	20638		140
41	Jan-21	20639		140
42	Jan-21	20640		140
43	Jan-21	20641	150
44	Jan-21	20642		140
45	Jan-21	20643		140
Sheet6

Cell Formulas
Range	Formula
H4:M5	H4	=FILTER(A2:F45,(MONTH(A2:A45)=MONTH(H3))*(YEAR(A2:A45)=YEAR(H3))*((C2:C45<>"")+(F2:F45<>"")),"")
O4:T5	O4	=FILTER(A2:F45,(MONTH(A2:A45)=MONTH(O3))*(YEAR(A2:A45)=YEAR(O3))*((C2:C45<>"")+(F2:F45<>"")),"")
Dynamic array formulas.

Thank you I tried this formula, and it seems to work. I am going to try it on the entire table first, then try it on the larger file. If you don't mind, I'll let you know how it turns out.

 

[Vince549]

Thank you I tried this formula, and it seems to work. I am going to try it on the entire table first, then try it on the larger file. If you don't mind, I'll let you know how it turns out.

I used the formula you gave me and I am only picking up the first column, nothing from the second column.

 

[JamesCanale]

Show me.

 

[Vince549]

Show me.

Just got it to work. I just needed to do both lines at the same time. I'm used to using Excel 2010 so the is a new function for me.