VBA Code to Remove Blanks and Print the sheet?

[Pisky]

I'm working on a project currently that requires me to sort a dataset into individual departments and print a list of products off for the department to check physical stock.

Currently my sheet splits into 7 different sheets, each with a separate department list

This works by sorting the "Dept #" column into each sheet, and showing nothing when the department number doesn't match that department (2013 excel... so no =FILTER)

I am wanting to automatically filter out the blanks in each row. For example with the attached sheet removing A2:Q9, A11:Q12 and so on, for each of the 7 departments sheets.

I then want to print columns A to L to the last row with figures on it (Basically the Print selection option) with the screenshotted options:

After it is sorted it needs to look like this:

01.08.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Store #	Store Name	Region	Dept #	Dept Name	Article #	Article Description 2	Qty	$ Amount	Floor	Reserve	Total	Unit Cost	Adjustment Amount	Reason	Adjustment Cost	Adjustment Code
10	2020	Test Store	2	898	Test Brand 1	6324271002	L+L PANT CLASSIC BLACK, Black, 92	-1	-89.99	0	0	0	89.99	1		89.99	1.6324271002
13	2020	Test Store	2	897	Test Brand 2	6672208005	L+LC SHIRT LS OXFORD CONTRAST, White, XL	-1	-69.99	0	0	0	69.99	1		69.99	1.6672208005
14	2020	Test Store	2	897	Test Brand 2	6679920001	L+LC SHIRT LS BRUSHED NAVY, Navy, XS	-1	-69.99	0	0	0	69.99	1		69.99	1.6679920001
22	2020	Test Store	2	832	Test Brand 3	6667802001	KATR SS SHIRT MURPHY WHITE, White, S	-1	-59.99	0	0	0	59.99	1		59.99	1.6667802001
30	2020	Test Store	2	877	Test Brand 4	6493535003	CHIS SWEATER JAMIE VEE NECK NAV, Navy, L	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6493535003
31	2020	Test Store	2	877	Test Brand 4	6611095003	CHIS SHORT EW CARGO LT BLUE, Lt Blue, L	-1	-49.99	0	0	0	49.99	1		49.99	1.6611095003
32	2020	Test Store	2	877	Test Brand 4	6616223004	CHIS ULTIMATE POLO YELLOW, Yellow, XL	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6616223004
33	2020	Test Store	2	877	Test Brand 4	6616224004	CHIS ULTIMATE POLO SAGE, Sage, XL	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6616224004
34	2020	Test Store	2	877	Test Brand 4	6628336005	CHIS SS SHIRT MASON WHITE, White, 2XL	-1	-49.99	1	0	1	49.99	2	Stocktake	99.98	2.6628336005
Mens

Cell Formulas
Range	Formula
L10,L13:L14,L22,L30:L34	L10	=IF(I10="","",K10+J10)
M10,M13:M14,M22,M30:M34	M10	=IF(I10="","",(I10/H10))
N10,N13:N14,N22,N30:N34	N10	=IFERROR(IF(L10="","",L10-H10),"")
P10,P13:P14,P22,P30:P34	P10	=IF(N10="","",N10*M10)
Q10,Q13:Q14,Q22,Q30:Q34	Q10	=IF(N10="","",N10&"."&F10)
F10,F13:F14,F22,F30:F34	F10	=IF('Raw Data'!J10=0,"",'Raw Data'!F10)
G10,G13:G14,G22,G30:G34	G10	=IF('Raw Data'!J10=0,"",'Raw Data'!G10)
H10,H13:H14,H22,H30:H34	H10	=IF('Raw Data'!J10=0,"",'Raw Data'!H10)
I10,I13:I14,I22,I30:I34	I10	=IF('Raw Data'!J10=0,"",'Raw Data'!I10)
C10,C30:C34,C22,C13:C14	C10	=IF('Raw Data'!J10=0,"",'Raw Data'!C10)
D10,D30:D34,D22,D13:D14	D10	=IF('Raw Data'!J10=0,"",'Raw Data'!D10)
A10,A13:A14,A22,A30:A34	A10	=IF('Raw Data'!J10=0,"",'Raw Data'!A10)

Here is the base sheet. Any help is appreciated.

01.08.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Store #	Store Name	Region	Dept #	Dept Name	Article #	Article Description 2	Qty	$ Amount	Floor	Reserve	Total	Unit Cost	Adjustment Amount	Reason	Adjustment Cost	Adjustment Code
2
3
4
5
6
7
8
9
10	2020	Test Store	2	898	Test Brand 1	6324271002	L+L PANT CLASSIC BLACK, Black, 92	-1	-89.99	0	0	0	89.99	1		89.99	1.6324271002
11
12
13	2020	Test Store	2	897	Test Brand 2	6672208005	L+LC SHIRT LS OXFORD CONTRAST, White, XL	-1	-69.99	0	0	0	69.99	1		69.99	1.6672208005
14	2020	Test Store	2	897	Test Brand 2	6679920001	L+LC SHIRT LS BRUSHED NAVY, Navy, XS	-1	-69.99	0	0	0	69.99	1		69.99	1.6679920001
15
16
17
18
19
20
21
22	2020	Test Store	2	832	Test Brand 3	6667802001	KATR SS SHIRT MURPHY WHITE, White, S	-1	-59.99	0	0	0	59.99	1		59.99	1.6667802001
23
24
25
26
27
28
29
30	2020	Test Store	2	877	Test Brand 4	6493535003	CHIS SWEATER JAMIE VEE NECK NAV, Navy, L	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6493535003
31	2020	Test Store	2	877	Test Brand 4	6611095003	CHIS SHORT EW CARGO LT BLUE, Lt Blue, L	-1	-49.99	0	0	0	49.99	1		49.99	1.6611095003
32	2020	Test Store	2	877	Test Brand 4	6616223004	CHIS ULTIMATE POLO YELLOW, Yellow, XL	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6616223004
33	2020	Test Store	2	877	Test Brand 4	6616224004	CHIS ULTIMATE POLO SAGE, Sage, XL	-1	-49.99	0	0	0	49.99	1	Stocktake	49.99	1.6616224004
34	2020	Test Store	2	877	Test Brand 4	6628336005	CHIS SS SHIRT MASON WHITE, White, 2XL	-1	-49.99	1	0	1	49.99	2	Stocktake	99.98	2.6628336005
35
36
37
38
39
40
41
Mens

Cell Formulas
Range	Formula
B35:B41,B23:B29,B15:B21,B11:B12,B2:B9	B2	=IF('Raw Data'!J2=0,"",'Raw Data'!B2)
C2:C41	C2	=IF('Raw Data'!J2=0,"",'Raw Data'!C2)
D2:D41	D2	=IF('Raw Data'!J2=0,"",'Raw Data'!D2)
E35:E41,E23:E29,E15:E21,E11:E12,E2:E9	E2	=IF('Raw Data'!J2=0,"",'Raw Data'!E2)
F2:F41	F2	=IF('Raw Data'!J2=0,"",'Raw Data'!F2)
G2:G41	G2	=IF('Raw Data'!J2=0,"",'Raw Data'!G2)
L2:L41	L2	=IF(I2="","",K2+J2)
M2:M41	M2	=IF(I2="","",(I2/H2))
N2:N41	N2	=IFERROR(IF(L2="","",L2-H2),"")
P2:P41	P2	=IF(N2="","",N2*M2)
Q2:Q41	Q2	=IF(N2="","",N2&"."&F2)
H3:H41	H3	=IF('Raw Data'!J3=0,"",'Raw Data'!H3)
I3:I41	I3	=IF('Raw Data'!J3=0,"",'Raw Data'!I3)
A2:A41	A2	=IF('Raw Data'!J2=0,"",'Raw Data'!A2)

 

[johnnyL]

Couple questions:

1) Is vba code an option?
2) What order do you want the columns sorted?

 

[Pisky]

Y

Couple questions:

1) Is vba code an option?
2) What order do you want the columns sorted

Yes VBA code is perfectly fine.

I just want the blank rows removed and all the columns pushed up, for example rows 2-9 would be removed and row 10 would now be row 2.

Similar to how adding filters and unticking blanks works if that makes sense.

Thank you

 

[johnnyL]

I think you need to include the 'Raw Data' sheet, otherwise the formulas on the 'Mens' sheet are going to crush the results.

 

[Pisky]

I

I think you need to include the 'Raw Data' sheet, otherwise the formulas on the 'Mens' sheet are going to crush the results.

wouldn’t think so. The raw data is the same data that in the 7 department sheets just split in to each department. Once it’s in the department sheet it shouldnt matter if the other lines are deleted would it? As long as the data shows as the condensed sheet I shared I’m happy with that

 

[johnnyL]

What I am saying is if I post the data that you have posted thus far for the 'mens' sheet ... the following is what the result is:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Store #	Store Name	Region	Dept #	Dept Name	Article #	Article Description 2	Qty	$ Amount	Floor	Reserve	Total	Unit Cost	Adjustment Amount	Reason	Adjustment Cost	Adjustment Code
2	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
3	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
4	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
5	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
6	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
7	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
8	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
9	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
10	#REF!	Test Store	#REF!	#REF!	Test Brand 1	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!
11	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
12	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
13	#REF!	Test Store	#REF!	#REF!	Test Brand 2	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!
14	#REF!	Test Store	#REF!	#REF!	Test Brand 2	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!
15	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
16	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
17	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
18	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
19	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
20	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
21	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
22	#REF!	Test Store	#REF!	#REF!	Test Brand 3	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!
23	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
24	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
25	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
26	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
27	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
28	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
29	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
30	#REF!	Test Store	#REF!	#REF!	Test Brand 4	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!		Stocktake
31	#REF!	Test Store	#REF!	#REF!	Test Brand 4	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!
32	#REF!	Test Store	#REF!	#REF!	Test Brand 4	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!		Stocktake
33	#REF!	Test Store	#REF!	#REF!	Test Brand 4	#REF!	#REF!	#REF!	#REF!	0	0	#REF!	#REF!		Stocktake
34	#REF!	Test Store	#REF!	#REF!	Test Brand 4	#REF!	#REF!	#REF!	#REF!	1	0	#REF!	#REF!		Stocktake
35	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
36	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
37	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
38	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
39	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
40	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
41	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!			#REF!	#REF!
42
Sheet1

Cell Formulas
Range	Formula
B35:B41,B23:B29,B15:B21,B11:B12,B2:B9	B2	=IF('Raw Data'!J2=0,"",'Raw Data'!B2)
C2:C41	C2	=IF('Raw Data'!J2=0,"",'Raw Data'!C2)
D2:D41	D2	=IF('Raw Data'!J2=0,"",'Raw Data'!D2)
E35:E41,E23:E29,E15:E21,E11:E12,E2:E9	E2	=IF('Raw Data'!J2=0,"",'Raw Data'!E2)
F2:F41	F2	=IF('Raw Data'!J2=0,"",'Raw Data'!F2)
G2:G41	G2	=IF('Raw Data'!J2=0,"",'Raw Data'!G2)
L2:L41	L2	=IF(I2="","",K2+J2)
M2:M41	M2	=IF(I2="","",(I2/H2))
N2:N41	N2	=IFERROR(IF(L2="","",L2-H2),"")
P2:P41	P2	=IF(N2="","",N2*M2)
Q2:Q41	Q2	=IF(N2="","",N2&"."&F2)
H3:H41	H3	=IF('Raw Data'!J3=0,"",'Raw Data'!H3)
I3:I41	I3	=IF('Raw Data'!J3=0,"",'Raw Data'!I3)
A2:A41	A2	=IF('Raw Data'!J2=0,"",'Raw Data'!A2)

 

[Pisky]

Ahh. Interesting. I’m at work currently and can’t install XL2BB on my company computer. Would you like me to maybe email the full spreadsheet if that will
Make it easier?

 

[johnnyL]

It can wait till you can post the XL2BB result.

 

[Pisky]

It can wait till you can post the XL2BB result.

Here is the Raw Data sheet (It goes further down than this)

The numbers under each department is how it currently sorts the data to the corresponding sheet =IF(K2=0,"",A2)

It was at the time the only way i could think about doing it with 2013 excel.

If there is another option I could do with VBA code too that would be awesome.

01.08.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Store #	Store Name	Region	Dept #	Dept Name	Article #	Article Description 2	Qty	$ Amount	Mens	Womens	Lingerie	Childrens	Toys	Home	Beauty	Unchecked
2	2020	Test Store	2	715	Oliver Black D715	6684754004	OB Wrap Coat - Deep Sea, 305 DGreen, 14	-1	-190	0	1	0	0	0	0	0	1
3	2020	Test Store	2	220	Shopping Bags D220	6398443	Reusable cotton tote bag	-65	-130	0	0	0	0	0	0	0	0
4	2020	Test Store	2	713	Whistle D713	6679786004	WH 3Qslv Wrap Dress - Rust, 223 Rust, 14	-1	-120	0	1	0	0	0	0	0	1
5	2020	Test Store	2	729	North South Womans D	6666279001	NS Turtle Neck Jumper - Kelp, XS, Green	-1	-120	0	1	0	0	0	0	0	1
6	2020	Test Store	2	729	North South Womans D	6668109003	NS Curved Hem Sweater - Black, M, Black	-1	-120	0	1	0	0	0	0	0	1
7	2020	Test Store	2	795	ONLY D795	6690595003	ONLY SEDONA LIGHT COAT Chocola, Brown, L	-1	-120	0	1	0	0	0	0	0	1
8	2020	Test Store	2	896	Calvin Klein Formal	6695595003	CK SHIRT SLIM LS MINI DIAMOND P, Tan, 40	-1	-100	0	0	0	0	0	0	0	0
9	2020	Test Store	2	726	Calvin Klein D726	6550028004	CKP High Waist Bike Short Blac, Black, L	-1	-100	0	1	0	0	0	0	0	1
10	2020	Test Store	2	898	Laidlaw+Leeds D898	6324271002	L+L PANT CLASSIC BLACK, Black, 92	-1	-90	1	0	0	0	0	0	0	1
11	2020	Test Store	2	715	Oliver Black D715	6690710002	OB LS Shell Top - Smudg, Multicolour, 10	-1	-80	0	1	0	0	0	0	0	1
12	2020	Test Store	2	713	Whistle D713	6683371006	WH LS Tiered Top - Navy Gin, BluePtn, 18	-1	-70	0	1	0	0	0	0	0	1
13	2020	Test Store	2	897	Laidlaw + Leeds Casu	6672208005	L+LC SHIRT LS OXFORD CONTRAST, White, XL	-1	-70	1	0	0	0	0	0	0	1
14	2020	Test Store	2	897	Laidlaw + Leeds Casu	6679920001	L+LC SHIRT LS BRUSHED NAVY, Navy, XS	-1	-70	1	0	0	0	0	0	0	1
15	2020	Test Store	2	373	Fayreform D373	6078674011	FAFO Lace Perfect Contour, Lt Brown, 12E	-1	-70	0	0	1	0	0	0	0	1
16	2020	Test Store	2	373	Fayreform D373	6666906022	FAFO Midnight Express U/W Br, Sepia, 14G	-1	-70	0	0	1	0	0	0	0	1
17	2020	Test Store	2	501	Charities D501	6557513	Look Good Feel Better Lipstick Pen	-16	-64	0	0	0	0	0	0	0	0
18	2020	Test Store	2	852	Cosmetics D852	7119219022	CLIN Sunkissed	-1	-62	0	0	0	0	0	0	1	1
19	2020	Test Store	2	713	Whistle D713	6666490003	WH Bengaline Pant Short -, Lt Brown, 12	-1	-60	0	1	0	0	0	0	0	1
20	2020	Test Store	2	715	Oliver Black D715	6665584001	OB 3Q Slv Double Layer Top - B, Black, 8	-1	-60	0	1	0	0	0	0	0	1
21	2020	Test Store	2	715	Oliver Black D715	6670982004	OB 3Q Slv Double Layer To, Blue Patt, 14	-1	-60	0	1	0	0	0	0	0	1
22	2020	Test Store	2	832	Kauri Trail D832	6667802001	KATR SS SHIRT MURPHY WHITE, White, S	-1	-60	1	0	0	0	0	0	0	1
23	2020	Test Store	2	373	Fayreform D373	6629422012	FAFO Felicity Underwire Bra, White, 14DD	-1	-60	0	0	1	0	0	0	0	1
24	2020	Test Store	2	346	Thermals D346	6514830003	ESTH Merino Ctn Lce Trim Sing, Cream, 14	-1	-55	0	0	1	0	0	0	0	1
25	2020	Test Store	2	310	Playtex D310	5143923024	PLTE Comfort Strap WF Bra Be, Beige, E24	-1	-55	0	0	1	0	0	0	0	1
26	2020	Test Store	2	852	Cosmetics D852	7602875003	CLIN Airbrush Concealer Fair Cream	-1	-52	0	0	0	0	0	0	1	1
27	2020	Test Store	2	852	Cosmetics D852	6014829002	CLIN Pop Lipstick Bare Pop	-1	-50	0	0	0	0	0	0	1	1
28	2020	Test Store	2	462	Drinkware D462	6251371	#S&P Bond Tartan S4 DOF Tumbler 250ml(9)	-1	-50	0	0	0	0	0	0	0	0
29	2020	Test Store	2	493	Pillows And Protecto	6618616	*HAES COMFORT EURO PILLOW (8)	-1	-50	0	0	0	0	0	1	0	1
30	2020	Test Store	2	877	Chisel Casual D877	6493535003	CHIS SWEATER JAMIE VEE NECK NAV, Navy, L	-1	-50	1	0	0	0	0	0	0	1
31	2020	Test Store	2	877	Chisel Casual D877	6611095003	CHIS SHORT EW CARGO LT BLUE, Lt Blue, L	-1	-50	1	0	0	0	0	0	0	1
32	2020	Test Store	2	877	Chisel Casual D877	6616223004	CHIS ULTIMATE POLO YELLOW, Yellow, XL	-1	-50	1	0	0	0	0	0	0	1
33	2020	Test Store	2	877	Chisel Casual D877	6616224004	CHIS ULTIMATE POLO SAGE, Sage, XL	-1	-50	1	0	0	0	0	0	0	1
34	2020	Test Store	2	877	Chisel Casual D877	6628336005	CHIS SS SHIRT MASON WHITE, White, 2XL	-1	-50	1	0	0	0	0	0	0	1
35	2020	Test Store	2	301	Bendon D301	6021086004	BEND Rita Contour Bra Black, Black, 12C	-1	-50	0	0	1	0	0	0	0	1
36	2020	Test Store	2	854	Ladies Fine Fragranc	6383654	ELAR My Fifth Avenue Body Lotion 150ml	-1	-46	0	0	0	0	0	0	1	1
37	2020	Test Store	2	302	Berlei D302	6543104014	BERL BT Luxe Contour Bra, Red Patt, 16DD	-1	-40	0	0	1	0	0	0	0	1
38	2020	Test Store	2	779	Mineral D779	6603882006	MIN DRESS MATCH IT - SLATE M, Slate, 2XL	-1	-40	0	1	0	0	0	0	0	1
39	2020	Test Store	2	655	3 - 7 Girlswear D655	6657718005	NOSO Merino L/S Frill Top Heart, Aqua, 7	-1	-40	0	0	0	1	0	0	0	1
40	2020	Test Store	2	675	3 - 7 Boyswear D675	6675853003	MAEL KNIT DENIM HIP STITCH - AC, Blue, 5	-1	-40	0	0	0	1	0	0	0	1
41	2020	Test Store	2	789	Scarves D789	6678934	BOBA CHECK SCARF - CAMEL	-1	-40	0	0	0	0	0	0	0	0
42	2020	Test Store	2	827	Accessories D827	6596945004	L+L BELT BOX BUCKLE MATTE/SI, Black, 2XL	-1	-40	1	0	0	0	0	0	0	1
43	2020	Test Store	2	401	Tabletop D401	6303843	Academy Eliot Wooden Scoop w/Leather Tie	-2	-40	0	0	0	0	0	1	0	1
44	2020	Test Store	2	373	Fayreform D373	6666907003	FAFO Midnight Express Full Bri, Sepia, L	-1	-40	0	0	1	0	0	0	0	1
45	2020	Test Store	2	182	Puma SOCKS D182	6679364001	PUMA SOCK 6PK SNEAKER ELMNT, Black, 7-9	-1	-35	0	0	0	0	0	0	0	0
46	2020	Test Store	2	674	Boys Thermals 8-16 D	6690967002	BL Merino Singlet Navy 8-14, Navy, 10	-1	-35	0	0	0	1	0	0	0	1
47	2020	Test Store	2	842	Champion D842	6591901	CHAMP CAP SCRIPT RED	-1	-35	1	0	0	0	0	0	0	1
48	2020	Test Store	2	646	Infants Accessories	6676632003	UWG Unicorn 3pk Socks-, Multicolour, 2-5	-2	-32	0	0	0	1	0	0	0	1
49	2020	Test Store	2	715	Oliver Black D715	6649538003	OB 3QSlv Crew Neck Cardigan - O, Pink, L	-1	-30	0	1	0	0	0	0	0	1
50	2020	Test Store	2	175	Mazzoni SOCKS D175	6628645001	MAZZ SOCK 5PK COTN STRIPES B, Blue, 7-11	-1	-30	0	0	0	0	0	0	0	0
51	2020	Test Store	2	384	Womens Sleepwear D38	6590320004	ZESS L/S COTTON TEE - PA, 450 L Blue, 14	-1	-30	0	0	1	0	0	0	0	1
52	2020	Test Store	2	769	Hosiery D769	5990119001	339 Pretty Plus Cotton Tight, Black, XL	-1	-30	0	0	1	0	0	0	0	1
53	2020	Test Store	2	827	Accessories D827	6516545	L+L TIE 7CM DIAMOND TEXTURE LT BLUE	-1	-30	1	0	0	0	0	0	0	1
54	2020	Test Store	2	333	Lyric D333	6620741002	LYR Boyleg Light Control Brie, Black, 12	-2	-30	0	0	1	0	0	0	0	1
55	2020	Test Store	2	849	Revlon D849	5794023001	REVL SL Lipstick Blushing Mauve	-1	-29	0	0	0	0	0	0	1	1
Raw Data

Cell Formulas
Range	Formula
J2:J55	J2	=COUNTIF('Dept Codes'!$A$3:$A$100,D2)
K2:K55	K2	=COUNTIF('Dept Codes'!$D$3:$D$100,D2)
L2:L55	L2	=COUNTIF('Dept Codes'!$G$3:$G$100,D2)
M2:M55	M2	=COUNTIF('Dept Codes'!$J$3:$J$100,D2)
N2:N55	N2	=COUNTIF('Dept Codes'!$M$3:$M$100,D2)
O2:O55	O2	=COUNTIF('Dept Codes'!$P$3:$P$100,D2)
P2:P55	P2	=COUNTIF('Dept Codes'!$S$3:$S$100,D2)
Q2:Q55	Q2	=SUM(J2:P2)

Here is the "Dept Codes" list too.

01.08.22.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Mens			Womens			Lingerie and Sleepwear			Childrens			Toys			Home			Beauty
2	Dept #	Dept Name		Dept #	Dept Name		Dept #	Dept Name		Dept #	Dept Name		Dept #	Dept Name		Dept #	Dept Name		Dept #	Dept Name
3	802	Bonds U/Wear		704	Bodycode D704		301	Bendon D301		637	Girls Hosiery D637		685	Toys D685		401	Stevens Tabletop		849	Revlon D849
4	804	Chisel U/WEAR		705	Bodycode Curve		302	Berlei D302		641	Bonds D641					402	Kitchenworks D402		851	Fast Moving Cosmetic
5	805	Jockey U/WEAR		709	Jigsaw		303	Calvin Klein		644	Girls Sleepwear 8-16					403	Home Décor		852	Cosmetics D852
6	806	Mazzoni U/WEAR		712	Ella J Regular D712		306	Jockey D306		645	Boys Sleepwear 8-16					451	Beverages & Toasters		853	Fragrance
7	809	Champion U/WEAR		713	Whistle D713		308	Nancy Ganz D308		646	Infants Accessories					452	Food Electrics D452		854	Ladies Fine Fragranc
8	812	Herschel		715	Oliver Black D715		309	Perfects D309		647	Infants Outerwear D6					453	Personal Care D453		855	Hair
9	818	Slippers		720	Puma D720		310	Playtex		648	Girls Sleepwear 2-8					458	Home Accessories D45		856	Mens Fine Fragrance
10	827	Accessories D827		725	Studio Curve D725		312	Sloggi		649	Infants Sleepwear D6					459	Lighting D459		857	Body & Bath D857
11	828	Bonds SLEEPWEAR		726	Calvin Klein		314	Bonds D314		652	Girls Underwear D652					465	Dinnerware		858	Skin D858
12	829	Gasoline D829		727	Zest Resort Curve D7		331	Honey Vegas D331		655	Girlswear 3-7 Years					466	Bakeware		305	Essence D305
13	831	Connor D831		729	North South Womans D		332	Me. By Bendon D332		656	8 - 14 Girlswear D65					469	Serveware D469
14	832	Kauri Trail D832		731	Vero Moda D731		333	Lyric D333		657	Girls Thermals 8-16					471	Cookware
15	835	Savane Casual		740	Zest Resort		345	Lyric Curve D345		663	Nursery Equip/Toys D					472	Kitchen Accessories
16	837	North South		741	Champion D741		346	Thermals D346		664	Nursery Manchester D					473	Laundry/Storage D473
17	839	Canterbury		747	Zest D747		347	Lingerie Accessories		667	Baby Feeding D667					484	Stationery/Events D4
18	840	Chisel SLEEPWEAR D84		756	State of Play D756		371	Caprice D371		673	Boys Sleepwear 2-8 D					491	Bathroom Textiles D4
19	842	Champion D842		770	Accessories		373	Fayreform D373		675	Boyswear 3-7 Years					493	Pillows and Protectors
20	843	Gym Equipment D843		776	Gifting		376	Triumph		676	Boyswear 8-14 Years					494	Sheets/Pillowcases D
21	845	Puma		779	Mineral D779		383	Acapella D383		678	Boys Hosiery D678					495	Duvets/Bedspreads
22	862	Jockey SLEEPWEAR D86		795	ONLY D795		384	Womens Sleepwear D38		665	Baby Care					114	Rugs
23	863	Mr Vintage D863		859	Sunglasses D859		769	Hosiery D769		674						117	Leather Furniture D1
24	864	Tarnish Casual D864		767	Socks D767		305	Essence D305		632						131	Dining Furniture
25	865	Mazzoni SLEEPWEAR D8								659						771	Luggage D771
26	867	King Size D867								631
27	871	Levis (Mens) D871
28	877	Chisel Casual D877
29	887	Bracks Formal D887
30	889	Chisel Formal D889
31	892	Farah D892
32	897	Laidlaw + Leeds Casu
33	898	Laidlaw+Leeds D898
34	162	Bonds SOCKS
35	166	Chisel SOCKS D166
36	170	Gym Equipment SOCKS
37	173	Jockey SOCKS
38	176	Mitch Dowd SOCKS
39	869	Logan D869
Dept Codes