[FONT="]Hello,[/FONT]
[FONT="]I have two tabs (Performance Period 1 [PP1] and Performance Period 2 [PP2]) with identical headers (range: B14:H14) and formats. They have two number columns (range: F14:H14) as well. Each table has over 1000 lines of data. Column A is left blank. the data on both tabs start with column B. [/FONT]
[FONT="]I want the two tabs consolidated into one Performance Period Summary [PPS], with duplicates removed and I want B14:H14 summarized (sumif'd). The two tables may have duplicate data.
[/FONT]
[FONT="]
[/FONT]
[FONT="]Below are the three tabs:[/FONT]
[FONT="]PERFORMANCE PERIOD1 tab - Data of performance period 1[/FONT]
[FONT="]PERFORMANCE PERIOD2 tab - Data of performance period 2[/FONT]
[FONT="]PERFORMANCE PERIOD SUMMARY tab - summary of performance period 1 and performance period 2. The table should not have duplicates and columns F and G should have totals of the two tables.
Thank you in advance.[/FONT]
[FONT="]
[/FONT]
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>[FONT="]
[/FONT]
****** id="cke_pastebin" style="position: absolute; top: 166px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody></body>
[FONT="]I have two tabs (Performance Period 1 [PP1] and Performance Period 2 [PP2]) with identical headers (range: B14:H14) and formats. They have two number columns (range: F14:H14) as well. Each table has over 1000 lines of data. Column A is left blank. the data on both tabs start with column B. [/FONT]
[FONT="]I want the two tabs consolidated into one Performance Period Summary [PPS], with duplicates removed and I want B14:H14 summarized (sumif'd). The two tables may have duplicate data.
[/FONT]
[FONT="]
[/FONT]
[FONT="]Below are the three tabs:[/FONT]
[FONT="]PERFORMANCE PERIOD1 tab - Data of performance period 1[/FONT]
[FONT="]PERFORMANCE PERIOD2 tab - Data of performance period 2[/FONT]
[FONT="]PERFORMANCE PERIOD SUMMARY tab - summary of performance period 1 and performance period 2. The table should not have duplicates and columns F and G should have totals of the two tables.
Thank you in advance.[/FONT]
[FONT="]
[/FONT]
PERFORMANCE PERIOD1 tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 350 | 25 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 135 | 3 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 80 | 3 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 90 | 10 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 160 | 1 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 205 | 4 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 250 | 7 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 295 | 10 | X-TREME |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
PERFORMANCE PERIOD2 tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 350 | 25 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 135 | 3 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 80 | 3 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 90 | 10 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 160 | 1 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 205 | 4 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 250 | 7 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 295 | 10 | X-TREME |
23 | USA0003 | 10.10.03 | Admin Specialist IV | LEV III | 340 | 13 | X-TREME |
24 | USA0003 | 10.10.03 | Construction Supervisor | LEV I | 385 | 16 | JINDO |
25 | USA0003 | 10.10.03 | Construction Foreman | LEV IV | 430 | 19 | JINDO |
26 | USA0003 | 10.10.03 | Estimator | LEV II | 475 | 22 | JINDO |
27 | USA0003 | 10.10.04 | Estimating Supervisor | LEV II | 520 | 25 | JINDO |
28 | USA0003 | 10.10.04 | Estimator | LEV III | 565 | 28 | SPARK |
29 | USA0003 | 10.10.04 | Estimator | LEV I | 610 | 31 | SPARK |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
PERFORMANCE PERIOD SUMMARY tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 700 | 50 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 270 | 6 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 160 | 6 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 180 | 20 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 320 | 2 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 410 | 8 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 500 | 14 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 590 | 20 | X-TREME |
23 | USA0003 | 10.10.03 | Admin Specialist IV | LEV III | 340 | 13 | X-TREME |
24 | USA0003 | 10.10.03 | Construction Supervisor | LEV I | 385 | 16 | JINDO |
25 | USA0003 | 10.10.03 | Construction Foreman | LEV IV | 430 | 19 | JINDO |
26 | USA0003 | 10.10.03 | Estimator | LEV II | 475 | 22 | JINDO |
27 | USA0003 | 10.10.04 | Estimating Supervisor | LEV II | 520 | 25 | JINDO |
28 | USA0003 | 10.10.04 | Estimator | LEV III | 565 | 28 | SPARK |
29 | USA0003 | 10.10.04 | Estimator | LEV I | 610 | 31 | SPARK |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
[/FONT]
****** id="cke_pastebin" style="position: absolute; top: 166px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
PERFORMANCE PERIOD1 tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 350 | 25 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 135 | 3 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 80 | 3 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 90 | 10 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 160 | 1 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 205 | 4 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 250 | 7 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 295 | 10 | X-TREME |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
PERFORMANCE PERIOD2 tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 350 | 25 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 135 | 3 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 80 | 3 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 90 | 10 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 160 | 1 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 205 | 4 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 250 | 7 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 295 | 10 | X-TREME |
23 | USA0003 | 10.10.03 | Admin Specialist IV | LEV III | 340 | 13 | X-TREME |
24 | USA0003 | 10.10.03 | Construction Supervisor | LEV I | 385 | 16 | JINDO |
25 | USA0003 | 10.10.03 | Construction Foreman | LEV IV | 430 | 19 | JINDO |
26 | USA0003 | 10.10.03 | Estimator | LEV II | 475 | 22 | JINDO |
27 | USA0003 | 10.10.04 | Estimating Supervisor | LEV II | 520 | 25 | JINDO |
28 | USA0003 | 10.10.04 | Estimator | LEV III | 565 | 28 | SPARK |
29 | USA0003 | 10.10.04 | Estimator | LEV I | 610 | 31 | SPARK |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>
PERFORMANCE PERIOD SUMMARY tab | |||||||
B | C | D | E | F | G | H | |
14 | PERF SITE | CHARGE CODE | JOB TITLE | JOB LEVEL | TOTAL HOURS | TOTAL HEADCOUNT | VENDOR |
15 | USA0001 | 10.10.01 | Utility Worker | LEV IV | 700 | 50 | ABX |
16 | USA0002 | 10.10.01 | Utility Specialist I | LEV II | 270 | 6 | ABX |
17 | USA0003 | 10.10.01 | Utility Specialist I | LEV II | 160 | 6 | ABX |
18 | USA0004 | 10.10.01 | Utility Specialist II | LEV III | 180 | 20 | ABX |
19 | USA0005 | 10.10.01 | Utility Supervisor | LEV I | 320 | 2 | ABX |
20 | USA0005 | 10.10.02 | Admin Specialist I | LEV IV | 410 | 8 | X-TREME |
21 | USA0005 | 10.10.02 | Admin Specialist II | LEV II | 500 | 14 | X-TREME |
22 | USA0005 | 10.10.02 | Admin Specialist III | LEV II | 590 | 20 | X-TREME |
23 | USA0003 | 10.10.03 | Admin Specialist IV | LEV III | 340 | 13 | X-TREME |
24 | USA0003 | 10.10.03 | Construction Supervisor | LEV I | 385 | 16 | JINDO |
25 | USA0003 | 10.10.03 | Construction Foreman | LEV IV | 430 | 19 | JINDO |
26 | USA0003 | 10.10.03 | Estimator | LEV II | 475 | 22 | JINDO |
27 | USA0003 | 10.10.04 | Estimating Supervisor | LEV II | 520 | 25 | JINDO |
28 | USA0003 | 10.10.04 | Estimator | LEV III | 565 | 28 | SPARK |
29 | USA0003 | 10.10.04 | Estimator | LEV I | 610 | 31 | SPARK |
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">
</tbody>