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

[PCRIDE]

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?

 

[PCRIDE]

Maybe MATCH would work finding all Account Names that match the criteria of Service Line: Production that are RED. ??

 

[PCRIDE]

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.

 

[PCRIDE]

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

 

[PCRIDE]

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!~

 

[Rick Rothstein]

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)

 

[PCRIDE]

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.

 

[PCRIDE]

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...)

 

[PCRIDE]

I think I need an INDEX MATCH formula?

 

[PCRIDE]

any Ideas?