Help with difficult formula requirement

[XMan]

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

 

[Ashish Mathur]

Hi,

Assume that the data is in range C1821. Sort D1821 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,SUMPRODUCT(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