• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formula Help

D

DME

Member
Greetings,

I have a Pivot table with values in cells A5 - B10000 (headers in Row 4). In the Pivot, Column A has the name of a Company (e.g. XYZ Company) and Column B has a count of that companies occurrences from a larger set of data (e.g. 3).

I'm attempting to extract the top 30 values - i.e. the top 30 Company counts - from the Pivot...and there will be duplicates. For example, Company XYZ and Company ABC may both show up 3 times.

In the same tab as the Pivot, I've setup the below formula in cells F5 - F34. In E5 - E34 I've listed out the numbers 1 through 30. The formula seems to work for my first value (i.e. 1) but isn't accurate after that one. Any thoughts on what I'm doing wrong? Is there a better way to do this? (NOTE: any formula I use should be based on the Pivot table setup, not the raw data)

{=IFERROR(INDEX($A$5:$B$10000,SMALL(IF(ISNUMBER(SEARCH(LARGE($B$5:$B$10000,E5),$B$5:$B$10000)),ROW($B$5:$B$10000)-ROW($B$4)),ROW(1:1)),1),"")}

Thanks in advance!
 
@Somendra Misra I'm unable to include a sample file with the same setup as my original but I've done my best to replicate it in the attachment. Please note that in my original file that the data set provided is a Pivot table and the numbers tied to each Company will change on an ongoing basis.
 

Attachments

You must log in or register to reply here.
Back
Top