Count unique occurrences in a list

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Hi guys,

I have a list as so:

Part1 Run1
Part1 Run1
Part2 Run1
Part3 Run1
Part4 Run2
Part4 Run2
Part1 Run3
Part2 Run3
Part5 Run3
Part6 Run4
Part1 Run5
Part1 Run5
Part1 Run5
Part6 Run5
....
Part1 Run1001
Part3 Run1001
Part3 Run1001
....

The number of unique parts may be >100 and the number of runs can be
anything (it's actually ~2000 in my current list)

What I would like to do is this:

For each Part, count how many Runs contained that Part (that is the
number of unique runs containing at least of that Part.)

I should end up with a new list similar to:

Part1 120
Part2 96
Part3 12
Part4 33
....

I know I could do this with VBA, but before that I wonder if there is
a way to do it easily with worksheet functions. Perhaps the easiest
way is with Access? (wrong group I know - if the answer here is yes
I'll look elsewhere.)

Thanks for reading guys and for any advice you may give

BBz
 
Bob said:
What I would like to do is this:

For each Part, count how many Runs contained that Part (that is the
number of unique runs containing at least of that Part.)

I should end up with a new list similar to:

Part1 120
Part2 96
Part3 12
Part4 33
Click to expand...

Assuming that Part and Run are in different columns then a simple Countif
looks like the solution.

If the Part and Run are in different columns then you could split them out
into seperate columns.

Create a list of unique Parts ( look at Data>Filter>Advanced Filter -
Unique Records only)
Then Countif(<column containing full list of Parts>,<part number from the
unique list>)

Andy
 
Create a list of unique Parts ( look at Data>Filter>Advanced Filter  -
Unique Records only)
Then Countif(<column containing full list of Parts>,<part number from the
unique list>)

Andy
Click to expand...

Thanks for the reply Andy,

However, will using CountIf in that fashion not simply give me a list
of the number of occurrences of each Part ID? This is not what I want
because a given Part ID might appear twice in one run - if this
happens, I want to count that as 1 Run. Perhaps I should have
formatted my output list as so:

Part1 120 (Part1 appears in 120 separate runs)
Part2 96 (Part2 appears in 96 separate runs)
Part3 12 (Part3 appears in 12 separate runs)
Part4 33 (Part4 appears in 33 separate runs)

Perhaps I've misunderstood your intention with CountIf?


Thanks again for your time
BBz
 
Thanks for the reply Andy,

However, will using CountIf in that fashion not simply give me a list
of the number of occurrences of each Part ID? This is not what I want
because a given Part ID might appear twice in one run - if this
happens, I want to count that as 1 Run. Perhaps I should have
formatted my output list as so:

Part1 120 (Part1 appears in 120 separate runs)
Part2 96 (Part2 appears in 96 separate runs)
Part3 12 (Part3 appears in 12 separate runs)
Part4 33 (Part4 appears in 33 separate runs)

Perhaps I've misunderstood your intention with CountIf?

No I misunderstood your intentions.
This is much simpler in Access, just a couple of queries.

Andy
 
See your other post in worksheet functions.
--
Jim Cone
Portland, Oregon USA



"Bob" <[email protected]>
wrote in message
Hi guys,

I have a list as so:

Part1 Run1
Part1 Run1
Part2 Run1
Part3 Run1
Part4 Run2
Part4 Run2
Part1 Run3
Part2 Run3
Part5 Run3
Part6 Run4
Part1 Run5
Part1 Run5
Part1 Run5
Part6 Run5
....
Part1 Run1001
Part3 Run1001
Part3 Run1001
....

The number of unique parts may be >100 and the number of runs can be
anything (it's actually ~2000 in my current list)

What I would like to do is this:

For each Part, count how many Runs contained that Part (that is the
number of unique runs containing at least of that Part.)

I should end up with a new list similar to:

Part1 120
Part2 96
Part3 12
Part4 33
....

I know I could do this with VBA, but before that I wonder if there is
a way to do it easily with worksheet functions. Perhaps the easiest
way is with Access? (wrong group I know - if the answer here is yes
I'll look elsewhere.)

Thanks for reading guys and for any advice you may give

BBz
 
Assuming that A2:B15 contains the data, first create a unique list of
part numbers. This can be done using...

Data > Filter > Advanced Filter > Unique records only

Then, let's say that D2:D4 contains the unique list (Part1, Part2, and
Part3), try...

E2, confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF($A$2:$A$15=D2,IF($B$2:$B$15<>"",MATCH("~"&$B$2:$B$15
,$B$2:$B$15&"",0))),ROW($B$2:$B$15)-ROW($B$2)+1),1))

Adjust the ranges, accordingly.
 
I use the following array formula to do that. It counts unique Unit Addresses
whether they are made up of text, number, or both, or blank cells (from my
range, called "Unit"):

=SUM(IF(FREQUENCY(IF(LEN(Unit)>0,MATCH(Unit,Unit,0),""),IF(LEN(Unit)>0,MATCH(Unit,Unit,0),""))>0,1))
 
Thanks for the help guys, appreciate it. Pretty CPU intensive on 200
Part IDs, but it gets there!

Many thanks again

BBz
 
Bob said:
Thanks for the help guys, appreciate it. Pretty CPU intensive on 200
Part IDs, but it gets there!

Many thanks again

BBz
Click to expand...


If you download and install the free add-in Morefunc.xll, you can use
the more efficient COUNTDIFF function. The add-in can be downloaded
here...

http://xcell05.free.fr/morefunc/english/

Note, however, the add-in is not compatible with Macintosh computers.
 
Back
Top