Complicated Consolidate sheet, How to get the results

[kizzie37]

I appreciate this is a lot of information I am just not sure how to achieve what i need, probably indexes & Macros. I have multiple workbooks, each workbook is for a plant type, each tab within the workbook is for a different size of the plant. I want to create a consolidated workbook that looks at each sheet and lists the company Name, the sizes of plant and the number allocated to them for each variety, and i need it to group together by company. (i.e. company A show all the sizes, varieties and allocations together in the list before listing the next company, it needs to pull from all the workbooks.

Below is a snippet of one of the tabs in one workbook (all tabs & workbooks are set out identically)
In the sheet ($F$2) lists the plant Size
AD$3 - AU$3 lists varieties of the plant
Column D ($D4) in the spreadsheet list company names
the Qtys to allocate are listed AD - AU , by company


Excel 2016 (Mac) 64 bit

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

AD​

[/th][th]

AE​

[/th][th]

AF​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td][/td][td]

Size​

[/td][td=bgcolor:#FFFF00]

5.0"​

[/td][td=bgcolor:#000000]

​

[/td][td=bgcolor:#000000]

​

[/td][td=bgcolor:#000000]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

Customer​

[/td][td]

Notes​

[/td][td]

Sum total Last ​

[/td][td=bgcolor:#DCE6F1]

glauca "Conica"​

[/td][td=bgcolor:#DCE6F1]

0​

[/td][td=bgcolor:#DCE6F1]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Ace[/td][td][/td][td]

360​

[/td][td=bgcolor:#DCE6F1]

25​

[/td][td=bgcolor:#DCE6F1]

​

[/td][td=bgcolor:#DCE6F1]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]company 2[/td][td][/td][td]

620​

[/td][td=bgcolor:#DCE6F1]

20​

[/td][td=bgcolor:#DCE6F1]

31​

[/td][td=bgcolor:#DCE6F1]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]Company 3[/td][td][/td][td]

0​

[/td][td=bgcolor:#DCE6F1]

​

[/td][td=bgcolor:#DCE6F1]

​

[/td][td=bgcolor:#DCE6F1]

45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td][/td][td]

0​

[/td][td=bgcolor:#DCE6F1]

​

[/td][td=bgcolor:#DCE6F1]

​

[/td][td=bgcolor:#DCE6F1]

​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Fall 5[/td][/tr][/table]


The final sheet should resemble something like this.

Excel 2016 (Mac) 64 bit

[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

Company​

[/td][td]

Genus​

[/td][td]

Size​

[/td][td]

Variety​

[/td][td]

Qty​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

Ace​

[/td][td]

​

[/td][td]

5.0"​

[/td][td]

glauca "Conica"​

[/td][td]

25​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

Ace​

[/td][td]

​

[/td][td]

5.0"​

[/td][td]

Variety 2​

[/td][td]

31​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

Company 2​

[/td][td]

​

[/td][td]

4.0"​

[/td][td]

Variety 1​

[/td][td]

5​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Consolidate[/td][/tr][/table]


Please note the same company may be on multiple sheets and workbooks, or they may only appear in one workbook & one tab

 

[kizzie37]

I am hoping someone will see this as an interesting challenge!

 

[tcardwell]

Hello kizzie37,

Since ...(all tabs & workbooks are set out identically)

All of your data should be in one workbook in one sheet and defined as a table. Once this is done, use a pivot table to do exactly what you have specified in the report requirements.

 

[kizzie37]

tcardwell, thats the problem I need to resolve though, how to get the data from all the sheets in all the workbooks into one table? There are many columns of data that I dont require, i just need to cherry pick the important pieces of data.

 

[tcardwell]

Power Query has a feature that can load data from many different workbooks at once, you may want to investigate how that works since it sounds like copy/paste values to one workbook would take forever. I wouldn't try to cherry pick the data on the front end, just delete it once you have moved everything.

 

[tcardwell]

I'm not a VBA expert but I'm sure guys on this board could solve the consolidating of all your data to one workbook pretty swiftly. I'm sure this is not the first time this problem has been encountered.

 

[kizzie37]

Thats my hope, the way the data is set out in the tabs is a little more complex than the example I have shown, I have just dhown the parts of the sheet that I want to draw from, copying and pasting directly from one sheet to another in its current form doesnt help me, as there are varying column lengths and numbers I just need the data in one large simple table, as this "info" needs to be collected then entered into an order system, hence why i want it grouped by cutomer, so accross all the workbooks I can see the plant types, varieties and qtys all in on place.

 

[tcardwell]

Power Query solution, give this a shot:
Consolidate Multiple Excel Workbooks Using Power Query - Free Microsoft Excel Tutorials

 

[kizzie37]

tcardwell, it looks interesting but I dont think its available for Mac