Formula / Function Problem
Many thanks. That works a treat!!
"T. Valko" wrote:
So, you want the count of unique job numbers for a specific machine code?
Job numbers (they are numbers, right?) in the range A2:A8
Machine codes in the range E2:E8
A10:A11 = machine codes A, B
Enter this array formula** in B10 and copy down to B11:
=COUNT(1/FREQUENCY(IF(E$2:E$8=A10,A$2:A$8),A$2:A$8))
If there might be empty cells in A2:A8 that correspond to a machine code
then use this version** :
=COUNT(1/FREQUENCY(IF(E$2:E$8=A10,IF(A$2:A$8<"",A$2:A$8)), A$2:A$8))
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
"TommoUK" wrote in message
...
I have a table of data that I carry out a number of calculations on.
However,
I am having a problem calculating one value. Below is a sample of the
data:
Job no U C P Machine MRs
88 1 3 1 A 1
88 0 3 2 A 1
88 0 3 3 B 1
99 1 4 1 A 1
99 0 4 2 A 1
99 0 4 3 B 1
99 0 4 4 B 1
U - identifies the number of jobs i.e. 88 is 1 job, 99 is another
C - identifies the number of parts to a job i.e. 88 has 3, 99 has 4
P - identifies the job part number i.e. line 1 is part 1 of job 88, line 2
is part 2 etc...
What I need to calculate is the number of jobs a machine worked on. In the
above example, I need to see:
Machine A = 2 (88 and 99)
Machine B = 2 (88 and 99)
Does anyone know of a formula or even a function that I could create in
VBA
that would calculate this number?
Thanks in advance.
|