formula to assign number to other numbers in array

[BSWJON]

Right ladies and gents here is my question of the week!

I need to determine how many individual kiln charges have been carried out in a month. The kiln charge numbers take the form of a 5 digit code. This code is entered into column B whenever data attributed to that charge is entered into the database. Therefore working out how many charges have been done is not simply a matter of counting the number of cell values, as one charge may have data entered 3 times, another 6, another once, etc.

An example of the data:
[TABLE="width: 297"]
<tbody>[TR]
[TD]Kiln Charge[/TD]
[TD]kiln [/TD]
[TD]date out[/TD]
[TD]Month[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 297"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]11035[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD="align: right"]11014[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD="align: right"]11035[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]11035[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD="align: right"]11014[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD="align: right"]11035[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18/03/2014[/TD]
[TD]March[/TD]
[/TR]
[TR]
[TD="align: right"]11011[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]13/03/2014[/TD]
[TD]March[/TD]
[/TR]
</tbody>[/TABLE]

So I am looking for a formula that counts the number of charges rather than the number of times a single charge has had data entered.

Off the back of this, I would like to display a chart showing how many charges of each pre-set charge length have occurred. I thought of just doing a chart which counted the number of charges and plotted them against cycle time data but again this doesnt work as it in fact counts all of the charge entries as single charges rather than grouping like charges.

I am probably not making any sense but if someone understands what I would like I would really appreciate the help.

Cheers

Jon

 

[DKaur]

How about:
=SUM(IF(FREQUENCY(A2:A9,A2:A9)>0,1))
where your data (not including header is in a2:a9?

 

[BSWJON]

I'll give that a crack, the charges are in column B so should it be B2:B9 etc etc?

 

[DKaur]

Yep

 

[BSWJON]

thank you DKaur, while your formula works well for knowing how many charges there are total, it doesn't physically number each charge.

Upon closer inspection the Rank formula works for me and what I need to do

 

[Peter_SSs]

Jon

Would Excel's built-in Pivot Table feature produce what you want - as shown here in columns H:I?

Excel Workbook
	B	C	D	E	F	G	H	I	J
1	Kiln Charge	kiln	date out	Month			Count of date out
2	11035	9	18/03/14	March			Kiln Charge	Total
3	11014	4	14/03/14	March			11011	1
4	11035	9	18/03/14	March			11014	2
5		4					11035	4
6	11035	9	18/03/14	March			Grand Total	7
7	11014	4	14/03/14	March
8	11035	9	18/03/14	March
9	11011	9	13/03/14	March
10
PT

 

[BSWJON]

That certainly shows me how many charges I have which is useful, more meaningful perhaps than just having the total generated by the formula.