UNIQUE, DUPS AND TRIP

Dsuperc · Nov 1st, 2002, 02:30 PM

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 · Nov 2nd, 2002, 07:47 AM

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 · Nov 4th, 2002, 02:49 PM

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 · Nov 4th, 2002, 02:58 PM

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 · Nov 4th, 2002, 03:31 PM

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 · Nov 4th, 2002, 03:40 PM

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!

Nov 2nd, 2002, 07:47 AM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,418	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. __________________ ~Anne Troy Office Articles

Nov 4th, 2002, 02:58 PM	#4
DonkeyOte MrExcel MVP Join Date: Sep 2002 Location: Suffolk, UK Posts: 7,979	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 __________________ Does my a$$ look big in this picture ?

Nov 4th, 2002, 03:40 PM	#6
DonkeyOte MrExcel MVP Join Date: Sep 2002 Location: Suffolk, UK Posts: 7,979	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! __________________ Does my a$$ look big in this picture ?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Nov 1st, 2002, 02:30 PM	#1
Dsuperc Join Date: Apr 2002 Posts: 133	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.

Nov 4th, 2002, 02:49 PM	#3
Dsuperc Join Date: Apr 2002 Posts: 133	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.

Nov 4th, 2002, 03:31 PM	#5
Dsuperc Join Date: Apr 2002 Posts: 133	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?