Hi,
Assume that the data is in range C18:D21. Sort D18:D21 in descending order.
In cell C27, type =C18. Then use the following formula in cell C28 and copy
down
=IF(C27="","",IF(COUNTIF(C$27:C27,C27)<VLOOKUP(C27 ,$C$18:$D$21,2,0),C27,IF(INDEX($C$18:$D$21,SUMPROD UCT(1/COUNTIF(C$27:C27,C$27:C27))+1,2)0,INDEX($C$18:$C$ 21,SUMPRODUCT(1/COUNTIF(C$27:C27,C$27:C27))+1,1),"")))
Hope this helps.
Adjust ranges for different sheets (as desired by you)
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"XMan" wrote in message
...
I have data with two columns on Sheet A as shown below. I need a formula
in column A of sheet B to display column A of sheet 1 the number of times
as indicated in column B of Sheet 1. Zero values should be excluded. I
gave an example of the output I need below
Sheet A
100 0
101 5
102 3
103 0
===============
Sheet B (Example of output I need) 101 is 5 times and 102 is 3 times. I
need a formula
on this sheet, and can not use VBA.
101
101
101
101
101
102
102
102