Unique Records #2

Hi

Hoping someone might be able to help.

I currently use the formula
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures
website to count unique records in data sets, where a record is
repeated in two or more rows. I create an additional column as
required, and use this field as my count data. Works a treat.

However I now have a large dataset (400000 records) for which this is
incredibly slow and painful. Is it possible to create a macro to do the
same thing, and if so how do I do this and will this speed things up?

Thanks in advance.

Cheers, Anthony

0
10/17/2006 10:05:36 AM
excel 39879 articles. 2 followers. Follow

3 Replies
547 Views

Similar Articles

[PageSpeed] 43

=SUMPRODUCT(--($A$2:$A2000<>""2)/COUNTIF($A$2:$A2000,$A$2:$A2000&""))

will count the total unique items

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Anthony" <anthony27981@hotmail.com> wrote in message
news:1161079536.435989.93070@m73g2000cwd.googlegroups.com...
> Hi
>
> Hoping someone might be able to help.
>
> I currently use the formula
> =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures
> website to count unique records in data sets, where a record is
> repeated in two or more rows. I create an additional column as
> required, and use this field as my count data. Works a treat.
>
> However I now have a large dataset (400000 records) for which this is
> incredibly slow and painful. Is it possible to create a macro to do the
> same thing, and if so how do I do this and will this speed things up?
>
> Thanks in advance.
>
> Cheers, Anthony
>


0
bob.NGs1 (1661)
10/17/2006 12:55:07 PM
Bob, please explain the need for
a) the 2 in ""2
and
b) the need for &""
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bob Phillips" <bob.NGs@somewhere.com> wrote in message 
news:OwY2Aue8GHA.1248@TK2MSFTNGP03.phx.gbl...
> =SUMPRODUCT(--($A$2:$A2000<>""2)/COUNTIF($A$2:$A2000,$A$2:$A2000&""))
>
> will count the total unique items
>
> -- 
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Anthony" <anthony27981@hotmail.com> wrote in message
> news:1161079536.435989.93070@m73g2000cwd.googlegroups.com...
>> Hi
>>
>> Hoping someone might be able to help.
>>
>> I currently use the formula
>> =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures
>> website to count unique records in data sets, where a record is
>> repeated in two or more rows. I create an additional column as
>> required, and use this field as my count data. Works a treat.
>>
>> However I now have a large dataset (400000 records) for which this is
>> incredibly slow and painful. Is it possible to create a macro to do the
>> same thing, and if so how do I do this and will this speed things up?
>>
>> Thanks in advance.
>>
>> Cheers, Anthony
>>
>
> 


0
bliengme5824 (3040)
10/17/2006 1:34:22 PM
The &"" is easy.  It is used to avoid a otherwise you get a #DIV/0 error if
there are any blank cells. The test for blanks is used to avoid counting
blanks as a unique item.

The 2 is not so easy, well actually it is, it is a typo, it shouldn't be
there.

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:%23Aqe1Df8GHA.2316@TK2MSFTNGP04.phx.gbl...
> Bob, please explain the need for
> a) the 2 in ""2
> and
> b) the need for &""
> best wishes
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Bob Phillips" <bob.NGs@somewhere.com> wrote in message
> news:OwY2Aue8GHA.1248@TK2MSFTNGP03.phx.gbl...
> > =SUMPRODUCT(--($A$2:$A2000<>""2)/COUNTIF($A$2:$A2000,$A$2:$A2000&""))
> >
> > will count the total unique items
> >
> > -- 
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Anthony" <anthony27981@hotmail.com> wrote in message
> > news:1161079536.435989.93070@m73g2000cwd.googlegroups.com...
> >> Hi
> >>
> >> Hoping someone might be able to help.
> >>
> >> I currently use the formula
> >> =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) from the Contextures
> >> website to count unique records in data sets, where a record is
> >> repeated in two or more rows. I create an additional column as
> >> required, and use this field as my count data. Works a treat.
> >>
> >> However I now have a large dataset (400000 records) for which this is
> >> incredibly slow and painful. Is it possible to create a macro to do the
> >> same thing, and if so how do I do this and will this speed things up?
> >>
> >> Thanks in advance.
> >>
> >> Cheers, Anthony
> >>
> >
> >
>
>


0
bob.NGs1 (1661)
10/17/2006 1:49:55 PM
Reply:

Similar Artilces:

Background color #2
I am creating a sheet where VBA creates new cells, I need to color the cell background, I just cannot seem to find anything that looks remotely like a fill or background function. I have been trying in the area of Range("A1"). Help would be very much appreciated. Thanks. Range("A1").Interior.ColorIndex = 6 will produce the yellow background color in A1 "Dave Griffiths" <dave@daveg.co.uk.nospam> wrote in message news:oPpUb.1899$rj4.27730@news2.e.nsc.no... > I am creating a sheet where VBA creates new cells, I need to color the > cell backgrou...

How can I define unique font per each Outlook profile?
We have several Outlook profiles on one computer and each user wants their own font. I noticed changing to one type of font in one profile affects all the other user profiles in Outlook. Is there a way to assign unique fonts to each Outlook profile? Many thanks experts... Each user would need their own Windows login.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstar...

How save selected record on subform
I have a subform which allows me to scroll through related records via navigation buttons, but I cannot work out how to make the record I want selected to display next time I visit this form - it always reverts back to the first record (not necessarily the one I want displayed). I must stress I have been learning by forums and trial & error. I have used the wizards where possible and only have a basic grasp of coding. Is there an easy solution? thanks in advance Bruce -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1 ...

unique record numbers
I am trying to create a unique PIN for members in a table (not the primary key) that will be 4 digits long, and would like each new entry to be incremental. I am very new, and would appreciate a detailed instruction (though I can get around access fair enough). Any help would be appreciated. On Fri, 5 Mar 2010 19:19:44 -0800, Rev David Bissas <Rev David Bissas@discussions.microsoft.com> wrote: The "DMax + 1" technique should work for you. Google or Bing for it. -Tom. Microsoft Access MVP >I am trying to create a unique PIN for members in a table (not t...

How to prevent 2 Note styles from merging?
My documentation includes a "Note" box and a "Tip" box that both derive from a "Note" style. That is, a "Note" has a horizontal line on the top and bottom of the paragraph, as does a "Tip" (a "Note" is supplemental information, whereas a "Tip" is an alternate way of doing the same task). I have many places in my document where a "Tip" immediately follows a "Note," and vice versa. The problem is that I have to separate the two paragraphs with an additional hard return. If I attempt to delet...

Beginner: Is HWND unique?
Hi! When I start an application twice and ask each application for the handle of its main window (calling an inbuild COM function), are the returned dwords guaranteed to be different? Thanks a lot for your help! Volker -- For email replies, please substitute the obvious. It is not guaranteed to be the same or different. You might get the same HWND towice in a row, or might get different ones between two instances of your program. AliR. "Volker Hetzer" <firstname.lastname@ieee.org> wrote in message news:ed9gei$di9$1@nntp.fujitsu-siemens.com... > Hi! > When I start an ...

Importing Visio into Access #2
Is it possible to import Visio 2007 into Access 2007? Thanks Joy You've asked the equivalent of "can I make an apple pie from oranges?". Check out Database Wizard in Visio help. Al <Joy.Huggins@gmail.com> wrote in message news:7a7da118-f30e-4b7b-8018-243523c78a24@k41g2000yqn.googlegroups.com... > Is it possible to import Visio 2007 into Access 2007? > > Thanks > > Joy "AlEdlund" <aledlund@comcast.net> wrote in message news:19594892-7535-44E6-8C8B-014A8043D7B1@microsoft.com... > You've asked the equivalent of "can I make ...

migrating from CRM 1.2 to new CRM 3.0 Server
Hello all, We are using CRM 3.0 and recently a new company has been acquired by our company , which uses crm 1.2. What is the best way to migrate data from new company to our existing crm 3.0 ? I can import accounts and contacts to our new system But what about activities and history? How is it possible? anyone having any comments? regards Aami as the old companies data will be tied to their Active Directory you are going to have to build a custom tool or buy in a solution to extract the actvities etc into a format you can import to your current system. =============================...

combo box to select record in a query
I'm creating an inventory database in Access 2000, and have my table and forms already setup. I have created a form with the source being "Department", with a sub form to show all the equipment list against that department. I want to create a drop down list, which shows all the departments, then moves to the department chosen. I have done this on my other forms successfully, where the form has been based on a table, and the ID field (Primary key) has been used in the combo box to search for the record. ID column is hidden in the combo box The source of the combo box is th...

exmerge #2
Setting up a new install of 2003... i got it all ready to go except 2 things 1st - when i use exmerge... the intial time i imported the data it only would copy my personal mailbox out of 13 attempted.... so now after some troubleshooting i get the error in the log ... says error opening message storage.... yes the service is running and yes i am a admin .... any suggestions 2nd - my OWA is not working properly and feedback on some good articles i can read bout it 1st. How to Assign Service Account Access to All Mailboxes in Exchange Server 2003 http://support.microsoft.com...

modify record
Hi, How can I modify a record, into a form, when I have more identical record in a table? The question is not clear. What do you mean by "modify a record, into a form"? Why are there identical records, and what do they have to do with the question? "Florin" <florin_serban@hotmail.com> wrote in message news:uPlho9m$HHA.4476@TK2MSFTNGP06.phx.gbl... > Hi, > > How can I modify a record, into a form, when I have more identical record > in > a table? > > > On Mon, 24 Sep 2007 08:54:33 +0300, "Florin" <florin_serban@hotmai...

find simillar variables in 2 different sheets
i have 2 different sheets with the same indentifier numbers am trying to figuer out how to get the same numbers "mohammed" wrote: > i have 2 different sheets with the same identifier numbers > am trying to figure out how to get the same numbers Just hazarding a hunch that what you're after is something to do with or which can be done with either VLOOKUP or INDEX/MATCH Try Debra Dalgleish's nice coverage on VLOOKUP or INDEX/MATCH at her: http://www.contextures.com/xlFunctions02.html VLOOKUP http://www.contextures.com/xlFunctions03.html INDEX/MATCH There&#...

Folder List
That's not very convenient is it? And since I didn't mention which version of Outlook I'm running (2000) I'll assume that goes for Office 2003 and XP as well. From: "Diane Poremsky [MVP]" <dianenws@poremsky.com> Subject: Re: Folder List - changing font size Date: Sunday, November 07, 2004 12:31 PM you don't unless you change it in Windows, which affects all programs. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Vis...

defining unique range of cells for different sheets as the same n.
I want to use the same name, month9, to refer the same range of cells, but on different sheets, how do i turn off the global define? When you define the name (Insert|name|Define), include the sheet name in the "names in workbook" box: sheet1!month9 or 'sheet 1'!month9 You can select your range and type this in the name box (to the left of the formula bar), too. KSAPP wrote: > > I want to use the same name, month9, to refer the same range of cells, but on > different sheets, how do i turn off the global define? -- Dave Peterson ...

401K contributions #2
When I try to set up my paycheck as gross income minus deductions, I can't get the 401K deduction to go into my 401K account in MM06. Has anybody else dealt with this one? TIA Mine goes fine. (Or went fine when I was doing my extended M06 trial.) More details please. Do you have the 401k investment cash account setup? Are you using Advanced Registers? How are you trying to get the 401k deduction to go into your 401k account from your paycheck? "Mediacom" <mhertz@mchsi.com> wrote in message news:DEcNg.42821$aJ.18680@attbi_s21... > When I try to set up my paychec...

How do I open word 2.0 files in word 2007?
What's the best way for a novice home user to open his Word 2.0 file in Word 2007? Word 2007 will not allow me to open them! See http://support.microsoft.com/kb/922849. On Fri, 5 Feb 2010 07:13:02 -0800, Word Help!! <Word Help!!@discussions.microsoft.com> wrote: >What's the best way for a novice home user to open his Word 2.0 file in Word >2007? > >Word 2007 will not allow me to open them! -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so ...

Locked records
I am having a bit of an annoying problem with my Access application. The data is held on a SQL 2000 server with Access front-end. I have a form (Form A) used for editing records, this form is launched from a command button on another form (Form B) and filters to a sepcific records on Form A dependant on the record in Form B. Nothing special here I use this technique all over this application. Form A opens up and displays the record I want but when ever I make a change and try to exit the form or save the record I get the following message: "The record has been changed by another user...

Resource Scheduling #2
Hi We are running Exchange 2000 and I have an issue that I'm struggling to resolve. We have a number of Public Folder Calendars in Exchange to monitor resource bookings.When scheduling appointments the user would like to invite the resource and be able to view the availability. Currently the public folders are mail enabled but the addresses are hidden from the GAL to avoid mail being indavertently sent to them. If I unhide the address from the GAL I am then select the relevant resource calendar in the scheduling view but the Free/Busy time is not available. I'm wondering if t...

Date format question #2
There is a date format that will format a date like this: "Wednesday, April 09, 2008" Can i create a custom format that will do this: Wed, Apr 09 If so, can you tell me how? Thanks, tom Select the cell, press Ctrl+1, select the number tab, select custom and type in: ddd, mmm dd Tyro "TomCon" <someone@verizon.net> wrote in message news:2ifGj.7437$Kw2.2142@trndny05... > There is a date format that will format a date like this: > "Wednesday, April 09, 2008" > Can i create a custom format that will do this: > Wed, Apr 09 > > If so, can...

Upgrade office mac #2
Version: Older version Operating System: Mac OS X 10.6 (Snow Leopard) I just bought a new iMAC computer with Snow Leopard; on my old Apple computer, I was using office mac 2001. This new computer will not let me install office mac 2001. Can I install an upgrade program for office 2004 on it; will that work? I really would rather not go to 2008 office. Thanks. Yes, if you can find a copy of the Office 2004 Upgrade package you can install it using your Office 2001 CD as the validation version -- just pop the CD in the drive & point the installer to it when prompted for the location of the ...

Is there any a "unique" function performs on CStringArray or CStringList?
Is there any a "unique" function performs on CStringArray or CStringList? I know there is an algorithm function "unique" performing on containers in Standard C++ library. Is there a similar function in MFC? What am I doing now to avoid adding same strings to container is: CStringList strList; ..... if (strList.Find("somestring") == NULL) strList.AddTail("somestring"); But I realized it may be an inefficient way. Any good sugestion? I would have used a CMap for this. Then, when all insertions were done, I might consider converting it to a CArray...

Importing from Excel to Outlook #2
When I try to import to Outlook from Excel I'm having a problem with zip/postal codes. All of my zips are in New Jersey and the first number of the zip is zero (0). I set the excel worksheet to recognize that column as "special, zip code" but when Outlook tries to pull in the data it's dropping off all the zeros leaving me with a 4 digit zip, sans the zeros. So frustrating. Please help! Thanks! Perhaps format the Excel field as text? "Bob" <Bob@discussions.microsoft.com> wrote in message news:E40573F0-B8CB-41CE-B946-8FB0E179ACF3@microsoft.com... > ...

Pull Unique Values From a List/Table
How can I pull unique values from a table? I track vendors who might sell multiple items, but I only need to show the vendor once so I can pull other information. There are also may be blanks in the rows if the vendor is inactive. Thanks! Let me add one more twist to this problem: The unique values also need to filter out where it may be 'VendorA - West' and 'VendorA - East' and combine them into one record. I only need it to show VendorA and not both records East and West (same company, just different drop ship location). Thanks! "Karl Burrows" <kfb1@spa...

can't open file #2
I created publisher 2000 files on a windows98 SE COMPUTER. I saved the files to disk. Just got a new Vista computer. I installed the publisher 2000 on the new computer. The disk shows the files but publisher says it cannot open them. Any hints? thanks -- joy artisan lampwork beads and jewelry http://wildorchidpotteryandart.com Try copying the saved files from the disk to the hard drive on your new computer and opening them from there. Bruce "wildorchid" wrote: > I created publisher 2000 files on a windows98 SE COMPUTER. I saved the files > to disk. Just got a new Vis...

Having users modify theier GAL record
I would like to provide a web based solution for allowing my users to modify thier GAL infomration. Any suggestions? I heard that there is a web based version of GALMOD. Is this true? Does it allow the user to modify all the fileds that GALMODCA allows? Another question about GALMOD. I saw a message somewhere stating that you should not use it if you are running Outlook2k3 in chached mode. Why Not? Just curiouse on this one. So back to my original Q. Is there a decent, non-complicated, web based product that will let my users changed thier info? Thanks, Steve F. ...