Counting repetitive numbers as 1

February 15, 2013 at 11:10:01
Specs: Windows XP
I am trying to calculate how many containers of goods are coming in per week. The spreadsheet containers the container numbers. So, let's say I have the following example:

XMYZZ
XMYZZ
XMYZZ
TO552

I would like a formula to calculate this as 2. 2 containers. Is there a way to do this?


See More: Counting repetitive numbers as 1

Report •

#1
February 15, 2013 at 12:25:58
Depends how the the data is stored and in what format..

In an SQL Database you can use Select Unique ...

But not knowing how that data is stored and in what format and answer will just be guess work.

Stuart


Report •

#2
February 15, 2013 at 14:22:13
Assuming excel ... you can use the native dedupe function in the Data ribbon and then COUNTA on the results.

If the list is sorted and starts on the 2nd row you can put the following rule in the adjacent column's cells

=IF(A2=A1,"",1)

and then sum the new column.

We'd need more information about your data and how it looks to give you a more specific answer.


Report •

#3
February 16, 2013 at 07:07:34
As AlteK says, we'd need more information about your data and how it looks to give you a more specific answer.

However, here's another suggestion that might work.

Let's say your data is in A1:A20, with no blank cells.

Try this formula:

=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))


If you want it make it a little fancier, and perhaps a little more automatic, create a Dynamic Mamed Range by using this formula in the "Refers To" field:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

A Dynamic Range Name will change it's length depending on how many rows of data are in the column.

(You can also just use a regular named range by creating one based on the actual data in the column at any given time. You just have to make sure that there are no blank cells in the range or the following formula won't work.)

Let's say you use the name "Container_Count"

Now use this formula in a cell:

=SUMPRODUCT(1/COUNTIF(Container_Count,Container_Count))

This would allow you paste each new list of containers into Column A and update the count.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question