Automatically copy range of cells to cells of same sheet

[toant]

Hi. I would sort by categories a range of cells to sub tables on the same sheet. I have attached a few pictures as examples to better explain my scenario. Please note "x" are just placeholders fields that some text will be entered later.

Criteria:

1) Automatically place each Item and range from Item==>Quantity it to each corresponding category "sub table" on the same sheet (i.e. all ice cream goes together and all Fruit goes together)
2) Any duplicate Items will not have another line but rather be combined and quantity added (note: highlighted quantity is just for reference that the quantity has been added)
3) If an Item is removed from the main table, it will automatically be removed from the sub table or substracted from the quantity.

Any help is appreciated. I would prefer not to use Macros for this.

Thank you in advance.

 

[toant]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also are you happy to change the layout of your data?

Thanks for the heads up. I updated the account.

The layout can be changed as long as the freezer column and shelf rows stay the same. The data on the bottom as to where the copied cells will go can be fully altered as long as they can be group someway. Ideally I want to show a sum and have a pie chart depiction of the categories so we can visually see the inventory.

 

[Fluff]

Thanks for that, can we get rid of the merged cells?
Also where you have an "x" will that always be the same for each item?

 

[toant]

Thanks for that, can we get rid of the merged cells?
Also where you have an "x" will that always be the same for each item?

Yes merged cells can be removed. x will be different values specific to each row such as lot number and expiration.

 

[Fluff]

x will be different values specific to each row such as lot number and expiration.

In that case which ones should be shown in the summary?

 

[toant]

In that case which ones should be shown in the summary?

Yes. I prefer them to be shown. Thank you.

 

[Fluff]

If you had something like this

Freezer .xlsx
	B	C	D	E	F
2	Freezer 1
3	Category	Item	Lot	Date	Quantity
4	Ice Cream	Vanilla	A	01/01/2021	5
5	Fruit	Strawberry	x	x	2
6	Meat	Chicken	x	x	4
7	Ice Cream	Vanilla	B	07/03/2021	7
8	Ice Cream	Vanilla	A	01/01/2021	6
Sheet1

How would you want it displayed?

Freezer .xlsx
	C	D	E	F
54	Option A
55	Vanilla	18
56
57
58	Option B
59	Vanilla	A	01/01/2021	11
60	Vanilla	B	07/03/2021	7
61
62
63	Option C
64	Vanilla	A	01/01/2021	18
Sheet1

 

[toant]

If you had something like this

Freezer .xlsx
	B	C	D	E	F
2	Freezer 1
3	Category	Item	Lot	Date	Quantity
4	Ice Cream	Vanilla	A	01/01/2021	5
5	Fruit	Strawberry	x	x	2
6	Meat	Chicken	x	x	4
7	Ice Cream	Vanilla	B	07/03/2021	7
8	Ice Cream	Vanilla	A	01/01/2021	6
Sheet1

How would you want it displayed?

Freezer .xlsx
	C	D	E	F
54	Option A
55	Vanilla	18
56
57
58	Option B
59	Vanilla	A	01/01/2021	11
60	Vanilla	B	07/03/2021	7
61
62
63	Option C
64	Vanilla	A	01/01/2021	18
Sheet1

option C

 

[Fluff]

Ok, how about

Freezer .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		Freezer 1					Freezer 2					Refrigerator 1					Freezer 4
3		Category	Item	x	x	Quantity	Category	Item	x	x	Quantity	Category	Item	x	x	Quantity	Category	Item		Expiration	Quantity
4	SHELF 1	Ice Cream	Vanilla	x	x	5	Vegetable	Peas	x	x	6	Meat	Steak	x	x	6					0
5		Fruit	Strawberry	x	x	2	Ice Cream	Chocolate	x	x	8	Fish	Halibut	x	x	7					0
6		Meat	Chicken	x	x	4	Meat	Steak	x	x	9	Meat	Chicken	x	x	5					0
7						0					0					0					0
8						0					0					0					0
9						0					0					0					0
10						0					0					0					0
11						0					0					0					0
12						0					0					0					0
13						0					0					0					0
14	SHELF 2	Fish	Halibut	x	x	2	Ice Cream	Vanilla	x	x	7	Vegetable	Broccoli	x	x	8					0
15		Vegetable	Corn	x	x	7					0	Fruit	Strawberry	x	x	5					0
16		Meat	Pork Ribs	x	x	5					0					0					0
17						0					0					0					0
18						0					0					0					0
19						0					0					0					0
20						0					0					0					0
21						0					0					0					0
22						0					0					0					0
23						0					0					0					0
24	SHELF 3					0					0					0					0
25						0					0					0					0
26						0					0					0					0
27						0					0					0					0
28						0					0					0					0
29						0					0					0					0
30						0					0					0					0
31						0					0					0					0
32						0					0					0					0
33						0					0					0					0
34	SHELF 4					0					0					0					0
35						0					0					0					0
36						0					0					0					0
37						0					0					0					0
38						0					0					0					0
39						0					0					0					0
40						0					0					0					0
41						0					0					0					0
42						0					0					0					0
43						0					0					0					0
44	SHELF 5					0					0					0					0
45						0					0					0					0
46						0					0					0					0
47						0					0					0					0
48						0					0					0					0
49						0					0					0					0
50						0					0					0					0
51						0					0					0					0
52						0					0					0					0
53						0					0					0					0
Sheet1

Cell Formulas
Range	Formula
F7:F13,U4:U53,P16:P53,K15:K53,F17:F53,P7:P13,K7:K13	F7	=D7*E7

Freezer .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
70			Ice Cream					Fruit					Meat
71			Item	x	x	Quantity		Item	x	x	Quantity		Item	x	x	Quantity
72			Vanilla	x	x	12		Strawberry	x	x	7		Pork Ribs	x	x	5
73			Chocolate	x	x	8							Steak	x	x	15
74													Chicken	x	x	9
75
76
77
78
79
80
81
82
83
84			Fish					Dairy					Vegetable
85			Item	x	x	Quantity		Item	x	x	Quantity		Item	x	x	Quantity
86			Halibut	x	x	9							Corn	x	x	7
87													Peas	x	x	6
88													Broccoli	x	x	8
89
90
91
92
93
94
95
96
Sheet1

Cell Formulas
Range	Formula
C72:F73	C72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=C70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
H72:K72	H72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=H70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
M72:P74	M72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=M70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
C86:F86	C86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=C84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
H86:K86	H86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=H84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
M86:P88	M86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=M84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
Dynamic array formulas.

 

[Fluff]

However it would be a lot simpler if you changed the layout like

Freezer .xlsx
	A	B	C	D	E	F
1
2		Freezer 1
3		Category	Item	x	x	Quantity
4	SHELF 1	Ice Cream	Vanilla	x	x	5
5		Fruit	Strawberry	x	x	2
6		Meat	Chicken	x	x	4
7						0
8						0
9						0
10						0
11						0
12						0
13						0
14	SHELF 2	Fish	Halibut	x	x	2
15		Vegetable	Corn	x	x	7
16		Meat	Pork Ribs	x	x	5
17						0
18						0
19						0
20						0
21						0
22						0
23						0
24	SHELF 3					0
25						0
26						0
27						0
28						0
29						0
30						0
31						0
32						0
33						0
34	SHELF 4					0
35						0
36						0
37						0
38						0
39						0
40						0
41						0
42						0
43						0
44	SHELF 5					0
45						0
46						0
47						0
48						0
49						0
50						0
51						0
52						0
53						0
54		Freezer 2
55		Category	Item	x	x	Quantity
56	SHELF 1	Vegetable	Peas	x	x	6
57		Ice Cream	Chocolate	x	x	8
58		Meat	Steak	x	x	9
59						0
60						0
61						0
62						0
63						0
64						0
65						0
66	SHELF 2	Ice Cream	Vanilla	x	x	7
67						0
68						0
69						0
70						0
71						0
72						0
73						0
74						0
75						0
76	SHELF 3					0
77						0
78						0
79						0
80						0
81						0
82						0
83						0
84						0
85						0
86	SHELF 4					0
87						0
88						0
89						0
90						0
91						0
92						0
93						0
94						0
95						0
96	SHELF 5					0
97						0
98						0
99						0
100						0
101						0
102						0
103						0
104						0
105						0
Sheet1 (2)

Cell Formulas
Range	Formula
F7:F13,F67:F105,F59:F65,F17:F53	F7	=D7*E7

Freezer .xlsx
	H	I	J	K
70	Ice Cream
71	Item	x	x	Quantity
72	Vanilla	x	x	12
73	Chocolate	x	x	8
74
75
76
77
78
79
80
81
82
Sheet1 (2)

Cell Formulas
Range	Formula
H72:H73	H72	=UNIQUE(FILTER(C4:C100,B4:B100=B57))
I72:J73	I72	=XLOOKUP($H72#,$C$2:$C$100,D2:D100,"",0)
K72:K73	K72	=SUMIFS(F:F,C:C,H72#)
Dynamic array formulas.

 

[toant]

Ok, how about

Freezer .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		Freezer 1					Freezer 2					Refrigerator 1					Freezer 4
3		Category	Item	x	x	Quantity	Category	Item	x	x	Quantity	Category	Item	x	x	Quantity	Category	Item		Expiration	Quantity
4	SHELF 1	Ice Cream	Vanilla	x	x	5	Vegetable	Peas	x	x	6	Meat	Steak	x	x	6					0
5		Fruit	Strawberry	x	x	2	Ice Cream	Chocolate	x	x	8	Fish	Halibut	x	x	7					0
6		Meat	Chicken	x	x	4	Meat	Steak	x	x	9	Meat	Chicken	x	x	5					0
7						0					0					0					0
8						0					0					0					0
9						0					0					0					0
10						0					0					0					0
11						0					0					0					0
12						0					0					0					0
13						0					0					0					0
14	SHELF 2	Fish	Halibut	x	x	2	Ice Cream	Vanilla	x	x	7	Vegetable	Broccoli	x	x	8					0
15		Vegetable	Corn	x	x	7					0	Fruit	Strawberry	x	x	5					0
16		Meat	Pork Ribs	x	x	5					0					0					0
17						0					0					0					0
18						0					0					0					0
19						0					0					0					0
20						0					0					0					0
21						0					0					0					0
22						0					0					0					0
23						0					0					0					0
24	SHELF 3					0					0					0					0
25						0					0					0					0
26						0					0					0					0
27						0					0					0					0
28						0					0					0					0
29						0					0					0					0
30						0					0					0					0
31						0					0					0					0
32						0					0					0					0
33						0					0					0					0
34	SHELF 4					0					0					0					0
35						0					0					0					0
36						0					0					0					0
37						0					0					0					0
38						0					0					0					0
39						0					0					0					0
40						0					0					0					0
41						0					0					0					0
42						0					0					0					0
43						0					0					0					0
44	SHELF 5					0					0					0					0
45						0					0					0					0
46						0					0					0					0
47						0					0					0					0
48						0					0					0					0
49						0					0					0					0
50						0					0					0					0
51						0					0					0					0
52						0					0					0					0
53						0					0					0					0
Sheet1

Cell Formulas
Range	Formula
F7:F13,U4:U53,P16:P53,K15:K53,F17:F53,P7:P13,K7:K13	F7	=D7*E7

Freezer .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
70			Ice Cream					Fruit					Meat
71			Item	x	x	Quantity		Item	x	x	Quantity		Item	x	x	Quantity
72			Vanilla	x	x	12		Strawberry	x	x	7		Pork Ribs	x	x	5
73			Chocolate	x	x	8							Steak	x	x	15
74													Chicken	x	x	9
75
76
77
78
79
80
81
82
83
84			Fish					Dairy					Vegetable
85			Item	x	x	Quantity		Item	x	x	Quantity		Item	x	x	Quantity
86			Halibut	x	x	9							Corn	x	x	7
87													Peas	x	x	6
88													Broccoli	x	x	8
89
90
91
92
93
94
95
96
Sheet1

Cell Formulas
Range	Formula
C72:F73	C72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=C70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
H72:K72	H72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=H70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
M72:P74	M72	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=M70),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
C86:F86	C86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=C84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
H86:K86	H86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=H84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
M86:P88	M86	=LET(Rng,(B4:U53),Rws,ROWS(Rng),Cols,5,Seq,SEQUENCE(COLUMNS(Rng)*Rws/Cols,,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,(INT(Seq/Rws))*Cols+SEQUENCE(,Cols)),Fltr,FILTER(FILTER(Ary,INDEX(Ary,,1)=M84),{0,1,1,1,0}),Uni,UNIQUE(INDEX(Fltr,,1)),IFERROR(CHOOSE({1,2,3,4},Uni,VLOOKUP(Uni,Fltr,2,0),VLOOKUP(Uni,Fltr,3,0),IF(ROWS(Fltr)=0,"",SUMIFS(F4:U53,C4:R53,Uni))),""))
Dynamic array formulas.

Thank you for your help! This worked perfectly.