Formula / Function Problem

[TommoUK]

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.

 

[dimm]

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.

 

[dimm]

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.

 

[T. Valko]

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)

 

[TommoUK]

Many thanks. That works a treat!!

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)

 

[T. Valko]

You're welcome. Thanks for the feedback!