Consolidating multiple tables from separate sheets?

[InOverMyH3ad]

Hi,
I am being asked to investigate a way to improve a very manual process we currently have at work. It wasn't too much of an issue for everyone to just slog through the manual process, but we recently had someone retire and the new person isn't as computer savvy as the previous person, so it is becoming somewhat burdensome. Anyways, the company I work for makes machines. When we ship those machines, we give them a list of recommended spare parts which is grouped by "should have on your shelf", "not horrible if you don't have on your shelf", and "you may never use spare part, but you could...". There are three departments that each have those lists, but when we send it to the customer it gets consolidated in to one list. I'm looking for a way to consolidate those 9 tables (3 tables per sheet) in to one table on a fourth sheet. I looked in to the consolidate function, but I'm not sure if that would work since each list will grow or shrink depending on the project and I would like to maintain some kind of formatting/spacing between departments and levels on the consolidated view. Is this something that could be done through a macro or VB? If so, if you can point me in the right direction of the best approach or functions to look in to I would be greatly appreciated.

I mocked up a quick example of how that info is entered and then consolidated in the likely scenario that my explanation didn't make much sense:

Example.xlsx

Shared with Dropbox

www.dropbox.com

 

[Dave Patton]

Try Vstack

VStack.xlsm
	M	N	O	P	Q
2	LEVEL 1	Apricot	0	$ 9.86	ea
3	LEVEL 1	Araza	0	$ 9.80	0
4	LEVEL 1	Avocado	0	$ 1.97	0
5	LEVEL 1	Banana	0	$ 4.48	0
6	LEVEL 1	Bilberry	0	$ 9.07	0
7	LEVEL 2	Miracle fruit	0	$ 1.75	0
8	LEVEL 2	Momordica fruit	0	$ 9.13	0
9	LEVEL 2	Monstera deliciosa	0	$ 3.84	0
10	LEVEL 2	Mulberry	0	$ 7.97	0
11	LEVEL 2	Nance	0	$ 7.42	0
12	LEVEL 2	Nectarine	0	$ 2.27	0
13	LEVEL 2	Orange	0	$ 10.95	0
14	LEVEL 2	Chico fruit	0	$ 3.94	0
15	LEVEL 2	Cloudberry	0	$ 1.40	0
16	LEVEL 2	Coco de mer	0	$ 5.48	0
17	LEVEL 2	Coconut	0	$ 3.34	0
18	LEVEL 2	Crab apple	0	$ 2.00	0
19	LEVEL 3	Japanese plum	0	$ 9.15	0
20	LEVEL 3	Jostaberry	0	$ 1.45	0
21	LEVEL 3	Jujube	0	$ 6.24	0
22	LEVEL 3	Juniper berry	0	$ 3.22	0
23	LEVEL 3	Kaffir Lime	0	$ 10.42	0
24	LEVEL 3	Kiwano (horned melon)	0	$ 10.03	0
25	LEVEL 3	Kiwifruit	0	$ 5.36	0
26	LEVEL 3	Kumquat	0	$ 8.05	0
27	LEVEL 3	Lemon	0	$ 1.11	0
28	LEVEL 3	Lime	0	$ 10.95	0
29	LEVEL 3	Loganberry	0	$ 1.91	0
30	LEVEL 3	Longan	0	0	0
3a

Cell Formulas
Range	Formula
M2:Q30	M2	=VSTACK(r_1,r_2,r_3)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
r_1	='3a'!$A$4:$E$8	M2
r_2	='3a'!$A$14:$E$25	M2
r_3	='3a'!$A$28:$E$39	M2

 

[Dave Patton]

The VStack should also work if the information is in actual Excel Tables.

VSTACK(T_1,T_2,T_3)

N.B. Do not use Merged cells with your data.