Consolidate and summarize data from multiple sheets into different workbook

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
526
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
My apologies for the drawn out post. I receive a workbook with statistics for three different departments, two sheets per department for a total of six sheets. I copy the stats from these sheets into a larger report file and would like to automate this part of the process as this is the only manual step remaining. The data I need to copy always begins in row 5 but the number of rows varies by department and month. The first department is the largest and has approximately 40 rows of data while the other two departments have 7 rows or less. Column A has the unit ID and column B has the stat count for that unit. The last row in column A is labeled Grand Total and the value in column B of that row is the total stat count of all of the units on that sheet.

As an example, the six sheets in the source data workbook are labeled ABC_INT, ABC_EXT, DEF_INT, DEF_EXT, GHI_INT, GHI_EXT (one internal and one external for each department). Below is a sample of how the data would appear in the original sheets for ABC_INT and ABC_EXT.
Excel 2010 32 bit
ABCDE
1
FROM SOURCE WORKSHEETS
2
ABC_INTABC_EXT
3
4
UNITCOUNTUNITCOUNT
5
ABC01
34
ABC01
58
6
ABC02
16
ABC02
5
7
ABC03
45
ABC03
6
8
ABC06
18
ABC04
23
9
ABC07
36
ABC05
18
10
ABC08
65
ABC06
13
11
ABC09
97
ABC07
5
12
ABC10
8
ABC08
2
13
ABC11
4
ABC09
3
14
15
ABC35
42
ABC34
81
16
Grand Total
752
ABC35
96
17
Grand Total
752

<tbody>
</tbody>
Sheet: Sheet3

<tbody>
</tbody>

Below is how I want the data to appear in the consolidated report. I want to copy/paste the data from the ABC_INT sheet into my report file. Then look at the data in the ABC_EXT sheet. If the unit ID in column A has a match for the data just copied, copy the stat value into column C of the matching row. Otherwise, insert a row for the unit ID, leave column B blank and copy the stat value into column C. Repeat these steps until everything from ABC_EXT has been copied and then skip a column and repeat the process for the last two departments.
Excel 2010 32 bit
ABCDEFGHIJK
20
DESIRED FINAL OUTPUT
21
ABC DEPT
DEF DEPT​
GHI DEPT​
22
UNITINTEXTUNITINTEXTUNITINTEXT
23
ABC01
34
58
DEF01
1,112​
35​
GHI01
16​
24
ABC02
16
5
DEF02
3​
GHI02
926
59​
25
ABC03
45
6
DEF03
6​
GHI03
45
1​
26
ABC04
23
DEF04
103​
66​
GHI04
2
970​
27
ABC05
18
DEF05
5​
86​
GHI05
115
84​
28
ABC06
18
13
DEF01
1​
1,040​
GHI06
44
2​
29
ABC07
36
5
DEF07
1​
4​
Grand Total
1,132
1,132​
30
ABC08
65
2
Grand Total
1,231​
1,231​
31
ABC09
97
3
32
ABC10
8
33
ABC11
4
34
35
ABC35
42
96
36
Grand Total
6,752
6,752​

<tbody>
</tbody>
Sheet: Sheet3


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
526
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Bump in the event anyone can help.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top