UNIQUE, DUPS AND TRIP

[Dsuperc]

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.

 

[Anne Troy]

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]

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]

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]

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]

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!