# Seeking method to compare (count) large data sets

#### auto.pilot

##### Well-known Member
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
212/31/2008
3Account Number
4131256
5162555
6138222
7569620
8159668
9992377
10Total 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
ABCDE
212/31/200812/31/200912/31/201012/31/201112/31/2012
3Account NumberCountCountCountCount
41312561111
51625550000
61382221100
75696201110
81596681000
99923771111
10Total of 140,000 account numbers
Sheet1

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

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

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.

Replies
6
Views
1K
Replies
3
Views
1K
Replies
1
Views
659
Replies
1
Views
1K
Replies
0
Views
280

1,203,672
Messages
6,056,670
Members
444,881
Latest member
Stu2407

### 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.

### Which adblocker are you using?

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

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