Showing a list of Accounts that are "RED" or "YELLOW" - Are you up to the challenge on this one!

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
907
Hi, I have a operations dashboard showing a status of each service within an account whether the status is RED, YELLOW or GREEN based on % that I have configured. So each account may have 25 services where a RED, YELLOW or GREEN status will show in that area. I have counted the number of Greens using CountIFs to give me a % of Green status for all that service across all accounts. Everything is fine there.

Now I need to display the Account names that are Red or Yellow.

Here is my layout - In each Service there could be up to 50 Accounts, each having a Status. Then I simple create a percent "Green"

Service | Status
Production | Green
Assembly | Yellow
Packaging | RED
Shipping | GREEN
Design | GREEN
Mechanical | GREEN


So in order to display the Accounts that make up the RED and YELLOW status, which could be multiple accounts, I need to display them next to the Service, in the same row. Or if anyone has better ideas feel free. The idea is to know which Accounts are making up the RED and YELLOW so those accounts can be looked at in detail as to why.

There could be 20 accounts RED, or YELLOW so I need to figure out a good way to display the Account Names.

I can't do a pivot table because I would need too many pivot tables for each Service and I don't have room. Excel seems to corrupt when you have too many pivot tables and charts sometimes
I was thinking a =Concatenate formula based on a CountIFs (Count the number of RED accounts, then Concatenate them) Account A, Account B, Account C etc... - Not sure if this can be done

The data in the spread sheet is refreshed using a data connection to Sharepoint so I have only 1 data table feeding the sheet. I can make helper fields and other tabs if needed.

Got any ideas?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Maybe MATCH would work finding all Account Names that match the criteria of Service Line: Production that are RED. ??
 
Upvote 0
One thing I tried is used another cell in the Data sheet,

If Account is RED, or YELLOW, Give me That account name

Now I have a list of Account names that are RED or Yellow in another column. I was thinking now I could just do a =CONCATENATE(Data!AI2:AI127) But apparently that doesn't work.
 
Upvote 0
I found this, seems to work but since I may have 500 rows of Accounts, of those only 10% could be red but throughout the rows. So I get ,,,,,,,,,,,,,,,,,Account Name A,,,,,Account Name B,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Account Name C

The number of commas seems to be the blank rows in the column.

Excel - columns to one cell - Super User
 
Upvote 0
Ok so that seems to work good.

How can I strip out the Commas (Using a formula) so the Account Names display a bit more nicer?

Thanks!~
 
Upvote 0
Ok so that seems to work good.

How can I strip out the Commas (Using a formula)
What did... the Concat function? May I suggest you use the Concat function I posted in my mini-blog article here
<!-- title / author block -->
Flexible Concatenation Function

It handles the multiple delimiters (commas in your case) automatically. While my function has more functionality than the one you referenced, it will work for what you want directly. Note, though, that the syntax is slightly different than the function you now have... my function requires you to specify the delimiter (any text may be used, not simply a comma) as the first argument and then follow that up with your range (or ranges). So, an example of calling my function with a comma delimiter for cells A1:A100 would be this...

=ConCat(",",A1:A100)
 
Upvote 0

What did... the Concat function? May I suggest you use the Concat function I posted in my mini-blog article here
<!-- title / author block -->
Flexible Concatenation Function

It handles the multiple delimiters (commas in your case) automatically. While my function has more functionality than the one you referenced, it will work for what you want directly. Note, though, that the syntax is slightly different than the function you now have... my function requires you to specify the delimiter (any text may be used, not simply a comma) as the first argument and then follow that up with your range (or ranges). So, an example of calling my function with a comma delimiter for cells A1:A100 would be this...

=ConCat(",",A1:A100)


NICE! This worked great. I wasn't sure if this could be done, but yet I guess anything can be done in Excel. Thanks Rick. I may come back with another question building on this thread if you don't mind.
 
Upvote 0
Is it possible to use MATCH or something that I can use ConCat with to pull in the region? Each account has a Region (NorthWest, SouthWest, North, West) etc... I'd like to say

=IF(A3="NorthWest",concat(Match "Northwests blah, blah...)
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,731
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top