Help with difficult formula requirement
 

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

Help with difficult formula requirement
 

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