Consolidate and summarize data from multiple sheets into different workbook

K

KGee

Well-known Member
Joined
Nov 26, 2008
Messages
539
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
1​
[/TD]
[TD]FROM SOURCE WORKSHEETS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
2​
[/TD]
[TD]ABC_INT[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC_EXT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
4​
[/TD]
[TD]UNIT[/TD]
[TD]COUNT[/TD]
[TD][/TD]
[TD]UNIT[/TD]
[TD]COUNT[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
5​
[/TD]
[TD]ABC01[/TD]
[TD]
34​
[/TD]
[TD][/TD]
[TD]ABC01[/TD]
[TD]
58​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
6​
[/TD]
[TD]ABC02[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD]ABC02[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
7​
[/TD]
[TD]ABC03[/TD]
[TD]
45​
[/TD]
[TD][/TD]
[TD]ABC03[/TD]
[TD]
6​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
8​
[/TD]
[TD]ABC06[/TD]
[TD]
18​
[/TD]
[TD][/TD]
[TD]ABC04[/TD]
[TD]
23​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
9​
[/TD]
[TD]ABC07[/TD]
[TD]
36​
[/TD]
[TD][/TD]
[TD]ABC05[/TD]
[TD]
18​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
10​
[/TD]
[TD]ABC08[/TD]
[TD]
65​
[/TD]
[TD][/TD]
[TD]ABC06[/TD]
[TD]
13​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
11​
[/TD]
[TD]ABC09[/TD]
[TD]
97​
[/TD]
[TD][/TD]
[TD]ABC07[/TD]
[TD]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
12​
[/TD]
[TD]ABC10[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD]ABC08[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
13​
[/TD]
[TD]ABC11[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]ABC09[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
14​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
15​
[/TD]
[TD]ABC35[/TD]
[TD]
42​
[/TD]
[TD][/TD]
[TD]ABC34[/TD]
[TD]
81​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
16​
[/TD]
[TD]Grand Total[/TD]
[TD]
752​
[/TD]
[TD][/TD]
[TD]ABC35[/TD]
[TD]
96​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]
752​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet3[/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
20​
[/TD]
[TD]DESIRED FINAL OUTPUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
21​
[/TD]
[TD]
ABC DEPT​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
DEF DEPT​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
GHI DEPT​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
22​
[/TD]
[TD]UNIT[/TD]
[TD]INT[/TD]
[TD]EXT[/TD]
[TD][/TD]
[TD]UNIT[/TD]
[TD]INT[/TD]
[TD]EXT[/TD]
[TD][/TD]
[TD]UNIT[/TD]
[TD]INT[/TD]
[TD]EXT[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
23​
[/TD]
[TD]ABC01[/TD]
[TD]
34​
[/TD]
[TD]
58​
[/TD]
[TD][/TD]
[TD]DEF01[/TD]
[TD]
1,112​
[/TD]
[TD]
35​
[/TD]
[TD][/TD]
[TD]GHI01[/TD]
[TD][/TD]
[TD]
16​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
24​
[/TD]
[TD]ABC02[/TD]
[TD]
16​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]DEF02[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]GHI02[/TD]
[TD]
926​
[/TD]
[TD]
59​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
25​
[/TD]
[TD]ABC03[/TD]
[TD]
45​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD]DEF03[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]GHI03[/TD]
[TD]
45​
[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
26​
[/TD]
[TD]ABC04[/TD]
[TD][/TD]
[TD]
23​
[/TD]
[TD][/TD]
[TD]DEF04[/TD]
[TD]
103​
[/TD]
[TD]
66​
[/TD]
[TD][/TD]
[TD]GHI04[/TD]
[TD]
2​
[/TD]
[TD]
970​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
27​
[/TD]
[TD]ABC05[/TD]
[TD][/TD]
[TD]
18​
[/TD]
[TD][/TD]
[TD]DEF05[/TD]
[TD]
5​
[/TD]
[TD]
86​
[/TD]
[TD][/TD]
[TD]GHI05[/TD]
[TD]
115​
[/TD]
[TD]
84​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
28​
[/TD]
[TD]ABC06[/TD]
[TD]
18​
[/TD]
[TD]
13​
[/TD]
[TD][/TD]
[TD]DEF01[/TD]
[TD]
1​
[/TD]
[TD]
1,040​
[/TD]
[TD][/TD]
[TD]GHI06[/TD]
[TD]
44​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
29​
[/TD]
[TD]ABC07[/TD]
[TD]
36​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD]DEF07[/TD]
[TD]
1​
[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]
1,132​
[/TD]
[TD]
1,132​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
30​
[/TD]
[TD]ABC08[/TD]
[TD]
65​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD]
1,231​
[/TD]
[TD]
1,231​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
31​
[/TD]
[TD]ABC09[/TD]
[TD]
97​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
32​
[/TD]
[TD]ABC10[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
33​
[/TD]
[TD]ABC11[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
34​
[/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD="bgcolor: #FFFF00"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
35​
[/TD]
[TD]ABC35[/TD]
[TD]
42​
[/TD]
[TD]
96​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
36​
[/TD]
[TD]Grand Total[/TD]
[TD]
6,752​
[/TD]
[TD]
6,752​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet3

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

E
  • Question Question
Total Usage by Site from Same Columns But Different Rows
Replies
2
Views
1K
miguel.escobar
M

Forum statistics

Threads
1,223,101
Messages
6,170,116
Members
452,302
Latest member
TaMere

Share this page

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