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
<tbody>
</tbody>
<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
<tbody>
</tbody>
<tbody>
</tbody>
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
| A | B | C | D | E | |
|---|---|---|---|---|---|
1 | FROM SOURCE WORKSHEETS | ||||
2 | ABC_INT | ABC_EXT | |||
3 | |||||
4 | UNIT | COUNT | UNIT | COUNT | |
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
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
20 | DESIRED FINAL OUTPUT | ||||||||||
21 | ABC DEPT | DEF DEPT | GHI DEPT | ||||||||
22 | UNIT | INT | EXT | UNIT | INT | EXT | UNIT | INT | EXT | ||
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>
