Excel VBA - Counting Unique Cells in a Range

To count unique values in a column in a range we can use:

=SUMPRODUCT((Table!D2:D1000<>"")/(COUNTIF(Table!D2:D1000,Table!D2:D1000)+(Table!D2:D1000="")))

That is if the data range is around 1k+ but what if i want to coun
lets say 60k records?? i tried using the above function to count 10
records and it hangs. Does anyone have solution to count up to 60
records?

--
Message posted from http://www.ExcelForum.com

0
7/6/2004 2:14:56 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
619 Views

Similar Articles

[PageSpeed] 17

Give the following a try. I didn't write it, so it'll probably work ;)

Cheers

Sub ExtractUniqueList()
Dim wksList As Worksheet
Dim rngList As Range, rngDest As Range
Dim iSortOrder As Integer
'********Change as appropriate ***************
'1=Ascending, 0 = unsorted, -1 = Descending
iSortOrder = iorder
Set rngDest = Worksheets("Sheet1").Range("D1")
Set wksList = Worksheets("Sheet1")
With wksList
 Set rngList = .Range(.Range("A1"), .Range("a65536").End(xlUp))
End With
'*********************************************************
Call ExtractList(UniqueList(rngList.Value, iSortOrder), rngDest)
Set rngDest = Nothing
Set rngList = Nothing
Set wksList = Nothing
End Sub

Sub ExtractList(vArray, rng As Range)
Dim vExtract()
Dim x As Long, lItems As Long
lItems = UBound(vArray)
ReDim vExtract(1 To lItems, 1 To 1)
For x = 1 To lItems
 vExtract(x, 1) = vArray(x)
Next
With rng
 .Resize(65537 - .Row, 1).ClearContents
 .Resize(lItems, 1).Value = vExtract
 End With
End Sub

Public Function UniqueList(vArray, Optional iorder As Integer = 1)
'Takes an array and gives a unique list as an array output
'iOrder determines the sort at the end:
' 1 = Ascending (Default)
' 0 = Unsorted
' -1 = Descending
Dim lItems As Long
Dim NoDupes As New Collection
Dim x As Integer, y As Integer, i As Integer, j As Integer
Dim Temp1, Temp2, vTemp()
On Error Resume Next
For lItems = LBound(vArray) To UBound(vArray)
 If Not (IsEmpty(vArray(lItems, 1))) Then
  If vArray(lItems, 1) <> "" Then _
   NoDupes.Add vArray(lItems, 1), CStr(vArray(lItems, 1))
 End If
Next lItems
On Error GoTo 0
lItems = NoDupes.Count
If iorder <> 0 Then
 For x = 1 To lItems - 1
  For y = x + 1 To lItems
   i = IIf(iorder < 0, y, x)
   j = IIf(iorder < 0, x, y)
   If NoDupes(i) > NoDupes(j) Then
    Temp1 = NoDupes(i)
    Temp2 = NoDupes(j)
    NoDupes.Add Temp1, before:=j
    NoDupes.Add Temp2, before:=i
    NoDupes.Remove x + 1
    NoDupes.Remove y + 1
   End If
  Next y
 Next x
End If
ReDim vTemp(1 To lItems)
For x = 1 To lItems
 vTemp(x) = NoDupes(x)
Next x
UniqueList = vTemp
End Function


"wuming >" <<wuming.18y9yu@excelforum-nospam.com> wrote in message
news:wuming.18y9yu@excelforum-nospam.com...
> To count unique values in a column in a range we can use:
>
>
=SUMPRODUCT((Table!D2:D1000<>"")/(COUNTIF(Table!D2:D1000,Table!D2:D1000)+(Ta
ble!D2:D1000="")))
>
> That is if the data range is around 1k+ but what if i want to count
> lets say 60k records?? i tried using the above function to count 10k
> records and it hangs. Does anyone have solution to count up to 60k
> records??
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004


0
macropod (18)
7/6/2004 11:09:40 AM
thats a lot of coding over there! i am a new user to excel vba and i du
think i can digest it immediately, however i would try to use the cod
if it works!
Thanks btw macropod! :

--
Message posted from http://www.ExcelForum.com

0
7/7/2004 8:50:55 AM
Reply:

Similar Artilces:

Working with Excel #2
Dear friends! I have the following problem - I am needing to represent in Excel a number with 20 (or more) digits. How can I do this? Many thanks! Hi Igor2005, > I have the following problem - I am needing to represent in Excel a number > with 20 (or more) digits. How can I do this? > That is only possible with either: - an addin (search google, there should be something for this, I recall seeing it but misplaced the link). - or by formatting the cell as text (but you won't be able to do math with the number easily then) Regards, Jan Karel Pieterse Excel MVP www.jkp-ad...

Lookup and Sum in same cell
SHNAME ENTDATE SYMBOL QTY RATE COMM C78 1/24/2005 CSCO 1100 0.018 $20 C78 1/28/2005 CSCO -5439 0.018 $100 C78 2/23/2005 CSCO 50 0.017 $1 C78 4/5/2005 CSCO -7892 0.018 $140 C78 4/13/2005 CSCO 3786 0.018 $69 I am trying to create a lookup formula that will search by SHNAME, find each entry and sum the COMM column in one cell: SHNAME COMM C78 $360 This worksheet is thousands of rows long with multiple rows for each SHNAME. Thanks in advance -- Potatosalad2 ------------------------------------------------------------------------ Potatosalad...

Practical Jokes in Excel?
Hello - What practical jokes have you played with Excel, either VBA or just built-in features? With all of the creativity on this Board, I figure there's got to be quite a few gems out there ... One of my coworkers thinks he knows it all, so I'd like to punk him ... my initial thought was a macro that would speak something (using text-to-speech) or play a sound when a specific key is pressed (say, the SHIFT or CTRL keys). I have absolutely no idea where to start with this ... thoughts? Looking forward to seeing what's been done before! //ray On Mar 2, 12:43=...

Cell Border Line Width
Is it possible to specify the cell border line width using Excel 2002? The "Border" tab in the "Format Cells" dialog box allows me to choose predefined line widths but I need a width other than what is offered. Is there a way to do this? Thanks! Adam Not with the borders property...you are restricted to xlHairline, xlThin, xlMedium, xlThick"....... You could *possibly* fake it with the AddLines method of the worksheet but I wouldn't really want to try.....;o) OJ ...

Excel 2007 and XML with inline schema
Hi. I have XML data that I want to be more user-friendly when its opened in excel. The user can create a web Query which will return the xml to them - ( pointing to the URL with the XML information)- and they can refresh the query each time they open the document in excel. Unfortunately- they have to format the data in excel manually. I wanted to associate an inline schema with the data to format the date cells, number cells etc. but the schema is not recognised in Excel. I have been just trying to get excel to recognise an inline schema- is this possible in the Excel? Any information regardi...

Excel 2000- protect one column- can only be modified by one person
I need to lock one column that can only be modified by one person. Can this be done? Joe There's nothing in Excel 2000 to set user range permissions. That feature was added in Excel 2002. fyi. -- Jim Rech Excel MVP "lunker55" <joec@shipwaystairs.com> wrote in message news:ObkUI8VAEHA.3936@TK2MSFTNGP11.phx.gbl... | I need to lock one column that can only be modified by one person. Can this | be done? | | Joe | | Thanks Jim "Jim Rech" <jrrech@hotmail.com> wrote in message news:O6t8%23rWAEHA.1420@TK2MSFTNGP11.phx.gbl... > There's nothing in E...

Excel loads slow
We just recently upgraded to XP SP2 with Office XP SP3. Before, on 2000, our users did not have a problem opening files from a certain directory. Now, while in Excel, the click the open folder and it takes 30-45 seconds for them to navigate to each folder in the directory. Any thoughts as to why XP is slower when drilling down through the folders? Thanks. Right-click on the My Computer icon on the Windows desktop, left-click on Disconnect Network Drive, and disconnect any mapped drives that are not currently available. Shane wrote: > We just recently upgraded to XP SP2 with Off...

project mgmt add on for excel
Is there a project management add on for excel inc. gant charts? ...

Excel For Mac 04-23-10
Several people in our company recently started to use MACs. They cannot open ..xlsx files we have created in Excel 2007. Does anyone know what the solution is to this issue. Thanks. We do not need to run macros. But they just need to open the files. Mike H. - Be sure Mac Excel 2008 has the most recent updates, currently 12.2.4, and then run the Mac disk utility to repair permissions. If you have the problem when you're using 12.2.4, I suggest posting in the very active newsgroup microsoft.public.mac.office.excel. To avoid offending some Mac users, be sure to re...

use variable value in a range reference?
My code is Dim myformulaTotalRows As Integer Dim mylastFormulaRow As Integer With Range("formulaCells") 'named range totalRows = .Rows.Count lastRow = .Rows(totalRows).Row End With myformulaTotalRows = totalRows mylastFormulaRow = lastRow Is there a way to use "mylastFormulaRow" in a statement like Range("C6").select where the row number 6 can be replaced by "mylastFormulaRow"? I want to use the selected cell as the first argument in a .filldown statement Thanks for any help Range("C&qu...

Locked cells in IE
Hi, I need to open an Excel document from a web page. Active X controls are restricted and I cant pass the document as a parameter in a shortcut to the Excel executable because it needs to be generic (and would not e in this case). The problem Im having is that the Excel document contains locked cells that need to remain locked. When the document opens within the internet explorer interface the locked cells are not visible and the spreadsheet looks a bit disjointed. Does anyone know another way to open the document from a web page or how to make the cells visible? Many many thanks...

Copy a word doc to excel and hyperlink data between the 2.
I have a chart on an excel document and a long word document explaining the data in the chart. I would like to place the word document under the chart in excel. Then I would like to hyperlink the data from the chart to specific places in the word document. Help.....I have been trying to figure it out for days. Thank you. ...

UPPER CASE formatting a cell
I receive excel data from a number of sources to be combined together in a single sheet. I would like to format a column of cells to be all upper case but not use the =UPPER() function. I'd like to format a cell to force any lower case char to upper case automatically when data is entered. Custom formatting as >CCCC doesn't work. Can this be done? Conversely, how can I force cell A1 to be UPPER CASE by using =UPPER(A2), then remove the reference to A2 when printing the result? I don't want the cell A2 to print. This I forget how to do. Hi you'll need VBA for this. ...

bmp files in Excel Hyperlinks open with wrong program
I have a list of tools in Excel, and I have used Excel's hyperlinking feature to allow displaying a picture of the tool by clicking the link. They always open with Paint, regardless of what I do in the "Folder Options >> File Types" box. I have customized the .BMP file type by adding "Edit with Paint" and "Edit with PhotoEditor" entries to the default settings. Does Excel look for the entry actually labeled "Open" or does it just grab the first entry from the list? (in this case, "Edit with Paint" is first, because they are ...

Excel 2007 convert into tab delimeter text file with extra tab spa
Hi , Sub: I have excel 2007 trying to save as tab delimeted text file having an issue. I have saved my excel 2007 as separate Tab delimited Text files. I see extra tabs stored in the tab delimited text files. But when I do the same with excel 2003 it works fine there is not extra tabs stored in my tab delimited text file. My excel 2007 conveted to tab delimited text file looks like as shown below(Extra two tabs at the end of the each record). Data0<Tab>data1<Tab><Tab> Data2<Tab>data3<Tab><Tab> Appreciate your help in advance. Rega...

find last cell in column
Hi, Apart from using VBA, is there any way to write a formula that will find the last used cell in a column of 15 cells? In the first set below, it would be ..388, in the second set it would be .133. Any help would be appreciated. 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.313 0.388 0.006 0.000 0.036 0.125 0.133 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 ...

Bug Report
On Mac ----------- Construct linear plot in Excel including: 1. Title 2. X-axis label 3. Y-axis label Copy and Paste into Word Save and send to PC On PC ---------- Open file in Word Notice Y-axis label is gone I'm a Biochemistry student who uses Excel to do lots of number crunching. I use a 1.25G Powerbook G4 with OS X 10.4.3 and the newest version of Office. I copy plots from Excel to Word often when writing reports without any problems. Recently, I've began submitting reports electronically via email and my professor uses a PC. When he opens my files, Y-axis labels are mis...

VBA Switching Between Macros
Hi, I have a lot of macros in my spreadsheet. And about 50% of the macros have variables that are passed through them, so for example the macros look like Macro1(x,y,z) rather then Macro1(). But usually to go to the code in a macro I look up the macro in the list on the toolbar. Bur these macros don't show up. Is there an easy way to go from one macro to the other's code. It is getting to be a pain searching through the different "modules" Thanks for your help What exactly is the problem? You can't see them in the list, but if they have parameters it presumabl...

Count Records for this week, this month, this quarter, this year
Hello, I am using the code-- =DCount("[ClaimID]","tblClaims","DateValue([DateCreated])=Date()") --to count the number of record created today. I would like to use a similar code to count the number of record created: 1) This week 2) This Month 3) This Quarter 4) This Year When I say “this”, I mean the current week, month, Quarter and Year based on NOW. For example, if I have 5 record for December 2009 and 6 records for December 2008, since we are in 2009 the total for “This Month” will be 5 (not 5 + 6 = 11). Also, my record CreatedDate has a dat...

Temporary Excel files.
Hi, does anyone know where Excel stores its temp files to managing multiple opened copies of the same workbook? Word stores its lock files in same directory of the opened document, but Excel? Thanks in advance for the answers. Alex On a single user (non-networked pc), excel will put a few files in your windows temp folder. But I think it does more than just that. (Just guessing!) When the workbook is on a network, excel/windows is smart enough to be able to tell that second person the the file is in use. If the only indicator were on the first user's temp file, then the seco...

Import into Access from Excel, passing a variable as the field...
Hello all, I have an Excel spreadsheet with a header that contains the exact same 30 names as the corresponding 30 fields in an Access DB table, and Row 2 below the Header contains the data I want to import. Instead, of physically typing all 30 fields in the code, I have a macro that loops through each "field" name that it got from the spreadsheet header. When I run this macro I get "run-time error '3265' Item not found in this collection', however, when I specify the actual field Name manually it works. This what I have: Dim iColumn As Integer Dim FieldName As ...

Tweaking cells
If I have cells that may remain blank, how do I get them to be blank instead of showing a 0? These cells are set for text, not numbers, so I don't understand why a number would show there by default. TIA Steven Connor Hi do you have formulas in these cells and if yes what kind of formulas? -- Regards Frank Kabel Frankfurt, Germany **{Steven}** wrote: > If I have cells that may remain blank, how do I get them to be blank > instead of showing a 0? These cells are set for text, not numbers, so > I don't understand why a number would show there by default. > > TIA >...

VBA to export large tables from Excel to SQL Server
I have spreadsheets that I distribute to users. I want Excel VBA to export large tables (2000+ rows) into a remote SQL Server 2005. I am able to successfully do this with the following generic code, but it is too slow: Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open "Driver={SQL Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;" con.Execute "INSERT INTO ... SELECT * FROM ..." 'Looping this for each row It takes over 10 minutes because it does it one line at a time. I've also tried l...

VBA and Protect Worksheet Dilemma
Hi, I am a novice at VBA and have struck a little problem. I have made a table in Excel that compares various Insurance Companies covers. I have checkboxes for the user to select which companies he/she wishes to compare and then a Compare button which takes the user to the worksheet with the comparisons on it showing only the chosen insurers. Here is my problem: I cannot protect the worksheets without getting a VBAerror. VBA cannot show columns that are hidden, or hide columns that are shown if the worksheet is protected. Can anyone assist me in how to overcome this issue, please? It ...

when inserting a chart excel is saying i cannot add any new fonts
When I attempt to insert a chart i am getting a message which says,"no new fonts may be added to this workbook". I am adding it to a billing process and I did the first 100 or so before it began this doing this, I am not adding any new fonts but I am getting this message, any suggestions on what is the cause so I can overcome it? I've described the problem and fix here: Error: "No more new fonts may be applied in this workbook" http://peltiertech.com/Excel/Charts/FixFonts.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltier...