Help with difficult formula requirement

X

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
 
A

Ashish Mathur

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,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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top