Select only the first N instances of a value in a table

[mark3824]

I feel like there are a hundred simple solutions to my problem but I can not come up with any!! I have a table full of patients and their Primary Care Providers. Some providers have 100 patients, some only have 30. I want to be able to select only the first 30 patients for each provider and make a new table.

One solution I thought of was to create a column that numbers the patients from 1 to N and then starts over when it gets to a new provider. For example, if I could go from image one (no index column) to image 2 (index column that starts over when it gets to a new provider), I would be home free. However, I can't figure out how to make an index column that resets to one when it gets to a new provider.

I would greatly appreciate any help on this. Like I said, there are probably many ways to get this done, but I've been thinking about it for weeks and searching forums, talking to AI, and I can't get a solution!

UPDATE: Replaced screenshots with .xlsx.

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[etaf]

how about
=COUNTIF($B$2:B2,B2)


but there maybe better ways to pull out your 30 clients
however, here is the index number you requested

how about a sample file with the 30 sample you want to extract and expected results... in case it can all be done with 1 formula and NO helper

I replied based on the images , i see now you have sample file - which is much better (for future , its always worth adding in manually the expected results)


now images have been removed

anyway - I have also added to your spreadsheet with the formula suggested and my sample
=COUNTIF($B$2:B2,B2)

[mark3824]

My hero! You're probably right there's a more elegant way to export my top 30, but this at least gets me there with much less brute force than previously required. Really appreciate the help!!!!

[TMS]

In cell C2, copied down

Please Login or Register  to view this content.

Then use:

Please Login or Register  to view this content.

Adjust the variable mc from 4 (for testing) to 30, or whatever.

[etaf]

you are welcome - TMS - has also shown how to filter that list - and as i say maybe a way to do in just one filter formula without the helper - But its late for me now - and going to be offline for a week - so unlikely to get back to this

[alansidman]

And yet one more way with Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.