Consolidate and summarize data from multiple sheets into different workbook

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
524
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,114,257
Messages
5,546,806
Members
410,759
Latest member
Bufnercash
Top