Combine Multiple Sheets

[Chris_010101]

I have a workbook with sheets for each financial year from 2010 onwards. All column headers are the same across all sheets.

I need to be able to combine all the sheets into one sheet *BUT* I need this combined sheet to change with the others if rows are added/deleted in any of the other sheets. I did play around with power query but I couldn't get anything that works the way I want it to.

I've been playing around with VSTACK but if I include blank rows in the range, they return as "0" in the combined sheet. I need the sheet to have all data in one block, not with loads of blank rows included in-between all the data, example:

Rows 15-30 are blank in the first sheet, but they return 0's in the VSTACK (column A is custom date formatted).

How do I get the formula to ignore blank rows, so that row 31 would be at row 15?

Thanks

 

[dreid1011]

You could use =FILTER(), and exclude where columns C, D or E = 0, or column B if you want to include the "N/A" employers.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Payroll Information						Payroll Information
2	Pay Month	Employer	Tax Code	Pay Frequency	Weekly Paid Hours		Pay Month	Employer	Tax Code	Pay Frequency	Weekly Paid Hours
3	10-Apr	N/A	0		0		45148	Howden Joinery	647	PAYE (Monthly)	44
4	10-May	N/A	0		0		45179	Howden Joinery	647	PAYE (Monthly)	44
5	10-Jun	N/A	0		0		45209	Howden Joinery	647	PAYE (Monthly)	44
6	10-Jul	N/A	0		0		45240	Howden Joinery	647	PAYE (Monthly)	44
7	10-Aug	Howden Joinery	647	PAYE (Monthly)	44		45270	Howden Joinery	647	PAYE (Monthly)	44
8	10-Sep	Howden Joinery	647	PAYE (Monthly)	44		44937	Howden Joinery	647	PAYE (Monthly)	44
9	10-Oct	Howden Joinery	647	PAYE (Monthly)	44		44968	Howden Joinery	647	PAYE (Monthly)	44
10	10-Nov	Howden Joinery	647	PAYE (Monthly)	44		44996	Howden Joinery	647	PAYE (Monthly)	44
11	10-Dec	Howden Joinery	647	PAYE (Monthly)	44		45027	Howden Joinery	747	PAYE (Monthly)	44
12	11-Jan	Howden Joinery	647	PAYE (Monthly)	44		45057	Howden Joinery	747	PAYE (Monthly)	44
13	11-Feb	Howden Joinery	647	PAYE (Monthly)	44		45088	Howden Joinery	747	PAYE (Monthly)	44
14	11-Mar	Howden Joinery	647	PAYE (Monthly)	44		45118	Howden Joinery	747	PAYE (Monthly)	44
15	Jan-00	0	0	0	0
16	Jan-00	0	0	0	0
17	Jan-00	0	0	0	0
18	Jan-00	0	0	0	0
19	Jan-00	0	0	0	0
20	Jan-00	0	0	0	0
21	Jan-00	0	0	0	0
22	Jan-00	0	0	0	0
23	Jan-00	0	0	0	0
24	Jan-00	0	0	0	0
25	Jan-00	0	0	0	0
26	Jan-00	0	0	0	0
27	Jan-00	0	0	0	0
28	Jan-00	0	0	0	0
29	Jan-00	0	0	0	0
30	Jan-00	0	0	0	0
31	11-Apr	Howden Joinery	747	PAYE (Monthly)	44
32	11-May	Howden Joinery	747	PAYE (Monthly)	44
33	11-Jun	Howden Joinery	747	PAYE (Monthly)	44
34	11-Jul	Howden Joinery	747	PAYE (Monthly)	44
Sheet2

Cell Formulas
Range	Formula
G3:K14	G3	=FILTER($A$3:$E$34,$C$3:$C$34<>0)
Dynamic array formulas.

 

[Fluff]

Are all the sheets you want to combine consecutive?
If so you could use something like

=LET(v,VSTACK('2010:2023'!A2:F100),FILTER(v,INDEX(v,,2)<>""))

 

[Chris_010101]

Are all the sheets you want to combine consecutive?
If so you could use something like

=LET(v,VSTACK('2010:2023'!A2:F100),FILTER(v,INDEX(v,,2)<>""))

Thanks again Fluff! With a little tweaking, it works perfectly

You really are a great help with excel and I really appreciate it

 

[Chris_010101]

You could use =FILTER(), and exclude where columns C, D or E = 0, or column B if you want to include the "N/A" employers.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Payroll Information						Payroll Information
2	Pay Month	Employer	Tax Code	Pay Frequency	Weekly Paid Hours		Pay Month	Employer	Tax Code	Pay Frequency	Weekly Paid Hours
3	10-Apr	N/A	0		0		45148	Howden Joinery	647	PAYE (Monthly)	44
4	10-May	N/A	0		0		45179	Howden Joinery	647	PAYE (Monthly)	44
5	10-Jun	N/A	0		0		45209	Howden Joinery	647	PAYE (Monthly)	44
6	10-Jul	N/A	0		0		45240	Howden Joinery	647	PAYE (Monthly)	44
7	10-Aug	Howden Joinery	647	PAYE (Monthly)	44		45270	Howden Joinery	647	PAYE (Monthly)	44
8	10-Sep	Howden Joinery	647	PAYE (Monthly)	44		44937	Howden Joinery	647	PAYE (Monthly)	44
9	10-Oct	Howden Joinery	647	PAYE (Monthly)	44		44968	Howden Joinery	647	PAYE (Monthly)	44
10	10-Nov	Howden Joinery	647	PAYE (Monthly)	44		44996	Howden Joinery	647	PAYE (Monthly)	44
11	10-Dec	Howden Joinery	647	PAYE (Monthly)	44		45027	Howden Joinery	747	PAYE (Monthly)	44
12	11-Jan	Howden Joinery	647	PAYE (Monthly)	44		45057	Howden Joinery	747	PAYE (Monthly)	44
13	11-Feb	Howden Joinery	647	PAYE (Monthly)	44		45088	Howden Joinery	747	PAYE (Monthly)	44
14	11-Mar	Howden Joinery	647	PAYE (Monthly)	44		45118	Howden Joinery	747	PAYE (Monthly)	44
15	Jan-00	0	0	0	0
16	Jan-00	0	0	0	0
17	Jan-00	0	0	0	0
18	Jan-00	0	0	0	0
19	Jan-00	0	0	0	0
20	Jan-00	0	0	0	0
21	Jan-00	0	0	0	0
22	Jan-00	0	0	0	0
23	Jan-00	0	0	0	0
24	Jan-00	0	0	0	0
25	Jan-00	0	0	0	0
26	Jan-00	0	0	0	0
27	Jan-00	0	0	0	0
28	Jan-00	0	0	0	0
29	Jan-00	0	0	0	0
30	Jan-00	0	0	0	0
31	11-Apr	Howden Joinery	747	PAYE (Monthly)	44
32	11-May	Howden Joinery	747	PAYE (Monthly)	44
33	11-Jun	Howden Joinery	747	PAYE (Monthly)	44
34	11-Jul	Howden Joinery	747	PAYE (Monthly)	44
Sheet2

Cell Formulas
Range	Formula
G3:K14	G3	=FILTER($A$3:$E$34,$C$3:$C$34<>0)
Dynamic array formulas.

Thanks for taking the time to respond dreid, really appreciate it.

Fluff's response was the one that worked. It basically allows me to select 300 rows on each sheet and won't include any blank rows in the data. Thus, then I can add something later on onto one of the sheets, it appears in "all data".

Hope that makes sense.

Formula is:

=IF(LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>""))=0,"",LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>"")))

The If formula then stops it returning "0" for blank cells.

Many Thanks

 

[Fluff]

If those cells actually contain a 0 on the original sheets then you can simply use

=LET(v,VSTACK('FY 2010-11:FY 2026-27'!A3:AD300),FILTER(v,INDEX(v,,2)<>0))