UNIQUE, DUPS AND TRIP

Dsuperc

Board Regular
Joined
Apr 3, 2002
Messages
135
I have a workbook with 65,000 records on sheet 1 and 45,000 records on sheet 2. Each with 10 columns.
Column A has the Social Insurance Number of the client, and Column B has an account number.
The Social Insurance number is duplicated and in some cases triplicate and even quad, while the account number is unique for each record.
Is there a way to separate records with “one social account number and one account number” from the others, and then further separate the “one social Insurance number and 2 accounts” from those with “1 Social Insurance number and 3 accounts” etc.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
Define "separate" please.

Have you tried Data-Subtotals?

Sort by the Social, then by the Acct.
Data-Subtotals.
Choose for each change in Social, use COUNT on Acct.
That should give you a list of everybody by Social, and tell you how many accounts they have.

Um. You may have to move some more data to a 3rd sheet so you have enough room for it to build the subtotals.
 

Dsuperc

Board Regular
Joined
Apr 3, 2002
Messages
135
By separate I mean “Move apart” may be on another page or separate from the others by a blank row.
If I use the Data-Subtotals, it will give me the desired result.
I did try “sub-total” but I got a message that the file was too big to perform that action.
Is there a code that would continue Sheet 1 onto sheet 2. I think some one posted such a code but I can’t find it now.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I am not trying to be funny but with that many records you could easily obtain your desired result by storing the data in Access and running SQL (far quicker and that's what it's built for, quick to set up and follow too) - by running queries on social sec. # columns - i.e. if SocSec1 is not blank and SocSec2 & 3 are = "" then you know you have an account with only 1 soc sec number - which would then be returned

My point is - if you run a loop or even formulas over 100,000+ rows of data you are going to have major memory issues (not to mention having to leave your PC alone for a good few minutes). If there's another way of doing using the filters etc I would be interested...

Apologies if this wasn't what you wanted to hear or if I have grabbed the wrong end of the stick...

If you do decide to hit the Access/SQL approach drop us a note and we'll help get started.

Luke
 

Dsuperc

Board Regular
Joined
Apr 3, 2002
Messages
135
Thanks very much for that info, and I appreciate your help. I will have to use Access as recommended.
Wanna give me some hints/details?
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
if you're on email send me a message and if you have any queries etc drop me a message and I'll help out!
 

Forum statistics

Threads
1,144,124
Messages
5,722,626
Members
422,448
Latest member
AugyIA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top