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.

Well theres the hard way and the easy way! ;-)

HARD WAY=

You can do it using a DO-LOOP, but it involves a bit of work....This is off
the top of my head, so bear with me! And someone more experienced may want to
tidy it up!! :-)
You meantioned VBA so I assume that you know how to open the VBA editor, add
a module, write a procedure and link it to a button?

Ok so.....

I can't make out your table very well so I'm assuming that your machine type
(A,B etc) is in column E from Row 5 downwards. (We need a few empty rows)

On your worksheet place a cell (We'll say cell A1 for the purpose of
demonstration) into which you can type the machine (A,B,... etc). If your ok
with the form controls this would be a lot neater using list buttons...but
this way will work.

In cell C1 type the following function:

=IF(A1="",2,IF(A1=B1,1,IF(B1="",2,0)))

Now place a button on your worksheet.

In your VBA module type the following.

Sub SelectMachine()
Application.ScreenUpdating = False

TestValue = Range("C1").Value
ResultValue = 0
RowValue = 4
Range("B1").Value = 1

Do Until TestValue = 2

RowValue = RowValue + 1
Rows(RowValue).Copy
Rows("3:3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E3").Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

TestValue = Range("C1").Value
If TestValue = 1 Then
ResultValue = ResultValue + 1
End If

Loop

Range("D1").Value = ResultValue
Rows("3:3").ClearContents
Range("A1").Select
Range("B1").Value = 1

Application.ScreenUpdating = True

End Sub
Link your button to the above procedure. Voila!

EASY WAY=

Select any column off to the right of your data (We'll say column G). We'll
keep cell A1 as the cell where you choose the machine designation your
looking for a count on. Again assuming your data starts at row 5, and machine
designations are in column E, click cell G5. Enter the following function:

=IF(E5=$A$1,1,"")

Now drag/copy this function straight down the column until the last line of
your data.

In any other empty cell enter the function:

=COUNT(G7:G29)

Now when you change the letter in cell A1, the count value will show the
total number you want.

NOTE: Neither of these methods are case sensitive.

Hope this helps. :-)

I just re-read your question......I thought you wanted the number of times
the machine was used overall, not just each job per machine. Sorry.

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.

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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"TommoUK" wrote in message
...
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.