Ranking based on frequency

Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8

I have the above data in range A1:L16. Data in range A1:B16 is the real data
and data in range C2:L16 is the work that I did it manually. Actually I am
looking for formulas in the range C2:L16.

I want to rank the numbers in column B based on their frequencies for all
the stages. For example, number in cell B2 is 2 hence the number 2 should
hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.

When you move to stage2, you should consider both stage1 and stage2. For
example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
the number which is at the bottom should get 1st rank therefore I have put
number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.

Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example, in
Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
(cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).

If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
even once, hence I have arranged them in ascening order for their ranks
(range H13:L13)

I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
able to work it out. I also tried writing a VBA code but I am getting
confused on counters.

A formula based solution will be appreciated but if it is not possible, then
excel vba/macro will also do.

Can anybody help me?

Try the following, which requires that you insert a blank column between
Column B and Column C...

1) First, let...

A1:B16 contain your data

Column C remain blank

D1:M1 contain the column labels for the ranking

2) Then, define the following names...

Select D2

Insert > Name > Define

Name: Array

Refers to:

=COUNTIF(MyRange,MyRange)+ROW(MyRange)/10^10

Click Add

Name: MyRange

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$16,ROWS(Sheet1!D$2:D2))

Click Add

Name: Nums

Refers to:

={0,1,2,3,4,5,6,7,8,9}

Click Add

Name: UniqueArray

Refers to:

=IF(MyRange<>"",IF(MATCH(MyRange,MyRange,0)=ROW(MyRange)-MIN(ROW(MyRange)
)+1,Array))

Click Add

Name: UniqueCount

Refers to:

=COUNT(1/(FREQUENCY(MyRange,MyRange)>0))

Click Ok

*Change the sheet reference accordingly.

3) Then, use the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

D2, copied down and across:

=IF(COLUMNS($D2:D2)<=UniqueCount,INDEX(MyRange,MATCH(LARGE(UniqueArray,CO
LUMNS($D2:D2)),Array,0)),INDEX(Nums,MATCH(0,COUNTIF($C2:C2,Nums),0)))

Hope this helps!

In article <[email protected]>,
"mac_see" <[email protected]> wrote:

> Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th
> Stage1,2,2,0,1,3,4,5,6,7,8,9
> Stage2,4,4,2,0,1,3,5,6,7,8,9
> Stage3,0,0,4,2,1,3,5,6,7,8,9
> Stage4,2,2,0,4,1,3,5,6,7,8,9
> Stage5,2,2,0,4,1,3,5,6,7,8,9
> Stage6,1,2,1,0,4,3,5,6,7,8,9
> Stage7,4,2,4,1,0,3,5,6,7,8,9
> Stage8,2,2,4,1,0,3,5,6,7,8,9
> Stage9,4,2,4,1,0,3,5,6,7,8,9
> Stage10,0,2,4,0,1,3,5,6,7,8,9
> Stage11,7,2,4,0,7,1,3,5,6,8,9
> Stage12,0,2,0,4,7,1,3,5,6,8,9
> Stage13,1,2,0,4,1,7,3,5,6,8,9
> Stage14,7,2,0,4,7,1,3,5,6,8,9
> Stage15,9,2,0,4,7,1,9,3,5,6,8
>
> I have the above data in range A1:L16. Data in range A1:B16 is the real data
> and data in range C2:L16 is the work that I did it manually. Actually I am
> looking for formulas in the range C2:L16.
>
> I want to rank the numbers in column B based on their frequencies for all
> the stages. For example, number in cell B2 is 2 hence the number 2 should
> hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.
>
> When you move to stage2, you should consider both stage1 and stage2. For
> example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
> the number which is at the bottom should get 1st rank therefore I have put
> number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.
>
> Whenever two or more numbers are of the same frequencies, then take the
> number at the bottom of the range/array as the top rank. For example, in
> Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
> 1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
> at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
> (cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
> hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).
>
> If a particular number is not appeared even once, then rank them in
> ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
> even once, hence I have arranged them in ascening order for their ranks
> (range H13:L13)
>
> I tried to use temporary columns with the RANK worksheet function with
> combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
> able to work it out. I also tried writing a VBA code but I am getting
> confused on counters.
>
> A formula based solution will be appreciated but if it is not possible, then
> excel vba/macro will also do.
>
> Can anybody help me?

Thanks Domenic

"Domenic" wrote:

> Try the following, which requires that you insert a blank column between
> Column B and Column C...
>
> 1) First, let...
>
> A1:B16 contain your data
>
> Column C remain blank
>
> D1:M1 contain the column labels for the ranking
>
> 2) Then, define the following names...
>
> Select D2
>
> Insert > Name > Define
>
> Name: Array
>
> Refers to:
>
> =COUNTIF(MyRange,MyRange)+ROW(MyRange)/10^10
>
> Click Add
>
> Name: MyRange
>
> Refers to:
>
> =Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$16,ROWS(Sheet1!D$2:D2))
>
> Click Add
>
> Name: Nums
>
> Refers to:
>
> ={0,1,2,3,4,5,6,7,8,9}
>
> Click Add
>
> Name: UniqueArray
>
> Refers to:
>
> =IF(MyRange<>"",IF(MATCH(MyRange,MyRange,0)=ROW(MyRange)-MIN(ROW(MyRange)
> )+1,Array))
>
> Click Add
>
> Name: UniqueCount
>
> Refers to:
>
> =COUNT(1/(FREQUENCY(MyRange,MyRange)>0))
>
> Click Ok
>
> *Change the sheet reference accordingly.
>
> 3) Then, use the following formula, which needs to be confirmed with
> CONTROL+SHIFT+ENTER, not just ENTER...
>
> D2, copied down and across:
>
> =IF(COLUMNS($D2:D2)<=UniqueCount,INDEX(MyRange,MATCH(LARGE(UniqueArray,CO
> LUMNS($D2:D2)),Array,0)),INDEX(Nums,MATCH(0,COUNTIF($C2:C2,Nums),0)))
>
> Hope this helps!
>
> In article <[email protected]>,
> "mac_see" <[email protected]> wrote:
>
> > Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th
> > Stage1,2,2,0,1,3,4,5,6,7,8,9
> > Stage2,4,4,2,0,1,3,5,6,7,8,9
> > Stage3,0,0,4,2,1,3,5,6,7,8,9
> > Stage4,2,2,0,4,1,3,5,6,7,8,9
> > Stage5,2,2,0,4,1,3,5,6,7,8,9
> > Stage6,1,2,1,0,4,3,5,6,7,8,9
> > Stage7,4,2,4,1,0,3,5,6,7,8,9
> > Stage8,2,2,4,1,0,3,5,6,7,8,9
> > Stage9,4,2,4,1,0,3,5,6,7,8,9
> > Stage10,0,2,4,0,1,3,5,6,7,8,9
> > Stage11,7,2,4,0,7,1,3,5,6,8,9
> > Stage12,0,2,0,4,7,1,3,5,6,8,9
> > Stage13,1,2,0,4,1,7,3,5,6,8,9
> > Stage14,7,2,0,4,7,1,3,5,6,8,9
> > Stage15,9,2,0,4,7,1,9,3,5,6,8
> >
> > I have the above data in range A1:L16. Data in range A1:B16 is the real data
> > and data in range C2:L16 is the work that I did it manually. Actually I am
> > looking for formulas in the range C2:L16.
> >
> > I want to rank the numbers in column B based on their frequencies for all
> > the stages. For example, number in cell B2 is 2 hence the number 2 should
> > hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.
> >
> > When you move to stage2, you should consider both stage1 and stage2. For
> > example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
> > the number which is at the bottom should get 1st rank therefore I have put
> > number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.
> >
> > Whenever two or more numbers are of the same frequencies, then take the
> > number at the bottom of the range/array as the top rank. For example, in
> > Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
> > 1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
> > at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
> > (cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
> > hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).
> >
> > If a particular number is not appeared even once, then rank them in
> > ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
> > even once, hence I have arranged them in ascening order for their ranks
> > (range H13:L13)
> >
> > I tried to use temporary columns with the RANK worksheet function with
> > combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
> > able to work it out. I also tried writing a VBA code but I am getting
> > confused on counters.
> >
> > A formula based solution will be appreciated but if it is not possible, then
> > excel vba/macro will also do.
> >
> > Can anybody help me?
>