Seeking method to compare (count) large data sets

[auto.pilot]

Working with Excel 2010, I have 5 separate sheets in one workbook. Each sheet is populated in column A with a series of account numbers that were open and active as of December 31 2008, 2009, 2010, 2011 and 2012. A sample is shown below.

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

 

[RonB1111]

Create a single 2 column list (700,000 rows) where Col A is the A/C # (copy/paste) and in Col B you manually type the year and copy/paste down to the end of that years data - repeat process for all years. Then use a pivot table to get your results. The initial pivot table calc may take just as long, but from the description of your data you won't have to refresh the pivot table often.

 

[auto.pilot]

I don't fully understand pivot tables, but I did stack up the account numbers as you suggested. The pivot table simply counts the number of accounts that are in each yearly list. Instead of this, I need to know which of the accounts that are in the 2008 file and still in each subsequent file 2009, 2010, 2011 & 2012. And, I need to know which year each account was closed. Counting the number of accounts in each year doesn't solve the problem.

Thanks for your post. I would appreciate some other thoughts since maybe I'm not using the pivot table correctly.

Jim

 

[RonB1111]

In the pivot table, did you place Col B in the "Columns Section"? The resulting display will then look like your desired results. You could then copy/paste value of the entire pivot table to another sheet and turn on AutoFilters to assist in analyzing which accounts were closed.