List Building Based on a Cell value or colour

[BeeL]

Hi I am trying to create a Macro/VB code that will build 4 lists based on a list (see attached example).

I have a list of applications and the numbers of users having them installed on their computers, these applications are marked using a RAG status (Red, Amber, Green & NBR No Business Requirement) on a seperate work sheet i want to split them by these RAG statuses, the status of applications changes as they are packaged for SCCM, so the RAG status will be changed, so i want the code to be able to rebuild the lists at any time to obtain an accurate view of the numbers at each status.

Any help would be appreciated as i am quite good with Excel but new'ish to coding.

Thanks for reading.

[nathansav]

FORUM_D17_RAG.xlsxDoes this help, no macro, array formulas

[BeeL]

I have just implemented your suggestion, work perfectly, great solution and quick and easy to maintain. Many thanks.

[nathansav]

No worries.

One thing to bear in mind is that the number of return rows has to be the same as the source, so 100 apps, 100 formulas, even though they could be 25% across each category.

[BeeL]

Thanks again, 1 question: What does the "ROWS(H$1:H2)-1" actually do?

[nathansav]

ROWS(H$1:H2)-1

This is the nifty bit. The whole thing relies on the small(array,k) formula, so the array is all the rows that meet the criteria, ROWS(H$1:H2)-1 increments by one each time, so dragging down it will be ROWS(H$1:H3)-1, ROWS(H$1:H4)-1, so it is inrementing the row count, which is incrementing our k value, which is the result you want small({1,2,6,8},1) gives 1small({1,2,6,8},4) gives 8

[BeeL]

Thanks Nathansav, i did manage to get it working across different sheets eventually and managed to work out what the ROWS(H...) was doing but I appreciate all your help and getting back to me.

Thanks again.