Counting groups of exact numbers in a huge list (column)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know how I can write a formula to count a very big list of numbers
and return the total number of numbers that are different in the list. For
example:
8800719
8800718
8800718
8800717
8800719
8800715
8800719
8800715
As you can see there are 8 numbers in this list but of those 8 there are
only 4 different case numbers. So if I had a list of several hundred or
thousand case numbers like the ones above, and many of them had duplicates
somewhere in the list, how can I get excel to give me a total number of all
the similar case numbers?
 
The first thing to do is to sort the list by the column holding the numbers.

The next thing to do is to give the list of numbers a name (Highlight the
list and select Insert..Name...Define)

In this example I have used the name NumberList. Assuming that the first
Number is in A2 then put this formula in B2

=IF(A2<>A1,COUNTIF(NumberList,A2),"DUPLICATE")

Then Fill Down .
Thereafter, you could autofilter on "Does not equal DUPLICATE" and copy the
list to another sheet.
 
So if I had a list of several hundred or
thousand case numbers like the ones above, and many of them had duplicates
somewhere in the list, how can I get excel to give me a total number of all
the similar case numbers?
Click to expand...

You may want to consider looking into PivotTables for this.
You could also try the Data | Subtotals features after Sorting your list
(also in the Data Menu) by Case Number, but with that many records, ut may be
too cumbersome to do it this way.

If you are new to PivotTables, this site has some good starter info:
http://peltiertech.com/Excel/Pivots/pivotstart.htm

As a note to my previous post: The Microsoft article claimed that the
Formula to count Unique Items was an Array, but I'm getting the Same result
by just pressing Enter as I am with Ctrl+Shift+Enter.

tj
 
The second problem with this is that I need to be able to do this without
sorting or resorting the data, only looking at the list as it gets bigger and
count all similar case numbers.
 
You're exactly right about using pivot tables, only not only have big lists
of case numbers, but everyday is a tab with hundreds of numbers and I didn't
want to get into making pivot tables for every tab, just needed to count all
matching numbers on every tab. You're previous formula did it, that's
exactly what I wanted. Thanks again.
 
This formula has been a great help to me. Thanks for the post; however what
if the items have an alpha-numeric coding (like automobile license plates)?
Is there an easy way to count unique labels in a list when the data in in a
text format?

Thanks
 
=SUMPRODUCT((A1:A11<>""(/COUNTIF(A1:A11,A1:A11&""))

--
HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting number of items excluding duplicates. 1
Counting groups of exact case numbers w/letters in them. 8
Counting multiple memberships 1
Counting the number of different entries in a column 2
Pivot tables - counting unique numbers 2
Counting a list 6
Excel VBA - counting cells by colors and names to it in two different rows. 0
Highlight exact match in column and count 2

Back
Top