Display same amount of values when narrowing a column

[crowds40]

Hi,

I hope my question isn't too complicated. So say in my column A I have:

blue
red
green
yellow
green
yellow
yellow
blue
...

Say in the end I have:
50 blue, 20 red, 100 green, and 180 yellow

If I am narrowing down the my amount of rows to just 100. How do I make it:
27 blue, 20 red, 27 green, 27 yellow
(I understand this is 101 which is fine or it could be 26 blue, 20 red, 27 green, 27 yellow so it uses the highest amounts to make it 100 if its easier to make a formula or macro)

Note that these amount will change each time I use excel. So next time I could start with:
15 blue, 30 red, 120 green, 200 yellow.

Again narrowing the amount to 100:
15 blue, 28 red, 28 green, 28 yellow (this equals 99, but to be 100 it could be 15 blue, 28 red, 28 green, 29 yellow)

So I would need a formula or macro which could display the same amount of colors, even if one of the colors is less then the average, to equal 100 total colors.
I hope this makes sense. I can try to explain further. Please help. Thank you.

 

[BiocideJ]

No, the hiding is going to happen on the same sheet as the sheet that colors are in column A.
So if the list of colors are in sheet 1, the hidden columns will also be in sheet 1.
If you want sheet 2 to be the one where everything happens, you will need to make sure Sheet 2 has a list of colors in column A and also that you change the sheet name in the section of my code that can be edited.

If you are needing things to happen across multiple sheets, you will need to tell me a little more about how your data is set up and what exactly you're needing to happen.

 

[crowds40]

Ok so it seems the the macro works great. Its perfect. Thanks you so much. I have one issue though. When I save the sheet and reopen it, it reverts back the the full list of colors. If I try to copy the all the colors after the macro is finished and paste into a new sheet it again pastes the original full amount of colors. Any ideas?

 

[BiocideJ]

I'm not so familiar with the functionality on Office for Mac, but this site gives a walk-through of copying only visible cells (which on PC seems to be the default).
https://www.utest.com/articles/how-to-select-just-the-visible-cells-in-excel-mac

Hopefully that helps.

 

[crowds40]

Awesome BiocideJ! You have been such a great help. Thank you so much for taking your time to help.