#### auto.pilot

##### Well-known Member

- Joined
- Sep 27, 2007

- Messages
- 734

- Office Version
- 365

- Platform
- Windows

Excel Workbook | |||
---|---|---|---|

A | |||

2 | 12/31/2008 | ||

3 | Account Number | ||

4 | 131256 | ||

5 | 162555 | ||

6 | 138222 | ||

7 | 569620 | ||

8 | 159668 | ||

9 | 992377 | ||

10 | Total of 140,000 account numbers | ||

Sheet1 |

On a separate worksheet, column A is populated with all of the account numbers that were open and active on December 31, 2008. In the shaded section, a simple COUNTIF formula determines if each account number is listed in each of the other worksheets (2009, 2010 etc). Given the sheer number of calculations (140,000 x 4), the workbook is slow to calculate and Excel sometimes becomes unresponsive. I am looking for an alternative method to perform this work and would appreciate any thoughts.

Thanks

Jim

Excel Workbook | |||||||
---|---|---|---|---|---|---|---|

A | B | C | D | E | |||

2 | 12/31/2008 | 12/31/2009 | 12/31/2010 | 12/31/2011 | 12/31/2012 | ||

3 | Account Number | Count | Count | Count | Count | ||

4 | 131256 | 1 | 1 | 1 | 1 | ||

5 | 162555 | 0 | 0 | 0 | 0 | ||

6 | 138222 | 1 | 1 | 0 | 0 | ||

7 | 569620 | 1 | 1 | 1 | 0 | ||

8 | 159668 | 1 | 0 | 0 | 0 | ||

9 | 992377 | 1 | 1 | 1 | 1 | ||

10 | Total of 140,000 account numbers | ||||||

Sheet1 |