Consolidating multiple tables from separate sheets?

InOverMyH3ad

New Member
Joined
Sep 17, 2018
Messages
9
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:
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Try Vstack

VStack.xlsm
MNOPQ
2LEVEL 1Apricot0 $ 9.86ea
3LEVEL 1Araza0 $ 9.800
4LEVEL 1Avocado0 $ 1.970
5LEVEL 1Banana0 $ 4.480
6LEVEL 1Bilberry0 $ 9.070
7LEVEL 2Miracle fruit0 $ 1.750
8LEVEL 2Momordica fruit0 $ 9.130
9LEVEL 2Monstera deliciosa0 $ 3.840
10LEVEL 2Mulberry0 $ 7.970
11LEVEL 2Nance0 $ 7.420
12LEVEL 2Nectarine0 $ 2.270
13LEVEL 2Orange0 $ 10.950
14LEVEL 2Chico fruit0 $ 3.940
15LEVEL 2Cloudberry0 $ 1.400
16LEVEL 2Coco de mer0 $ 5.480
17LEVEL 2Coconut0 $ 3.340
18LEVEL 2Crab apple0 $ 2.000
19LEVEL 3Japanese plum0 $ 9.150
20LEVEL 3Jostaberry0 $ 1.450
21LEVEL 3Jujube0 $ 6.240
22LEVEL 3Juniper berry0 $ 3.220
23LEVEL 3Kaffir Lime0 $ 10.420
24LEVEL 3Kiwano (horned melon)0 $ 10.030
25LEVEL 3Kiwifruit0 $ 5.360
26LEVEL 3Kumquat0 $ 8.050
27LEVEL 3Lemon0 $ 1.110
28LEVEL 3Lime0 $ 10.950
29LEVEL 3Loganberry0 $ 1.910
30LEVEL 3Longan000
3a
Cell Formulas
RangeFormula
M2:Q30M2=VSTACK(r_1,r_2,r_3)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
r_1='3a'!$A$4:$E$8M2
r_2='3a'!$A$14:$E$25M2
r_3='3a'!$A$28:$E$39M2
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,241
Messages
6,177,369
Members
452,773
Latest member
D P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top