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?

 

[Rick Rothstein]

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

I think the code I posted in this other mini-blog article of mine will do what you want (read the article as it explains the arguments to this function which are different than for the code in my previous article)...

<!-- title / author block -->LookUp Value and Concatenate All Found Results

 

[PCRIDE]

Thanks, I read through it and I am not sure it will do what I need based on how my sheet is set up

Here is my data table

I have a dedicated column for each Accounts service, so each Account has like 10 services.

Account Name | Service 1 | Service 2 | Service 3 | Service 4

Then each service has a RED, YELLOW or GREEN status.

Then I have a helper formulas and columns (10 Helper Columns), at the end of the data table (Data table is being populated through Sharepoint/SQL data connection)
The helper columns repeat | Service 1 | Service 2 | Service 3 | Service 4 etc... Then the formula says (speaking it for ease of understanding!)

First Helper Column If(Service1 = RED OR YELLOW, then return the Account Name
First Helper Column If(Service2 = RED OR YELLOW, then return the Account Name
First Helper Column If(Service3 = RED OR YELLOW, then return the Account Name
First Helper Column If(Service4 = RED OR YELLOW, then return the Account Name

Then I used your CONCAT formula to pull in all Account Name that met the condition RED or YELLOW

My issue is the accounts fall into a Region Eastern, North Eastern, Western, South Western etc...

The region drop down on my dashboard so when the user chooses "North Eastern" it will pull a % GREEN for that Service, in that Region using CountIFs (I need the multi-condition formula) (Count the number of Greens, In Eastern, in this column etc..)

So you see now that I have a list of all my accounts, I need to only CONACT the names of the accounts that are aligned to a region. Does this make sense? Sorry I can't post any pictures of it.

 

[PCRIDE]

Ok, FORGET everything I just said!! I got it working and surprised myself. I guess I wasn't thinking it though and was a bit confused on the formula structure.

OK now that its working and filtering on Regions by Status (GREEN, YELLOW, RED etc..)

The only problem is the formula /function is really slow and after choosing my Drop Down for the regions, excel does nothing for about 5 seconds, then if you touch excel it goes into Not Responding, but what its actually doing is calculating (Lower right corner status) then updates.

Is there anything that is causing it to become slow? I am using 2010, but some users might have 2013 if that makes a difference.

Any code I don't need ? I am only using the first 3 arguments in your formula.

 

[PCRIDE]

I think there might be extra code that I may not need causing it to slow down, but the workbook is not bigger than 70 rows and 50 columns.

Let me know what you think.

and thank you !!

 

[Rick Rothstein]

Any code I don't need ? I am only using the first 3 arguments in your formula.

I don't know how much faster the code might be (please tell us after you have run it), but here is the function with all the code related to the optional arguments (along with the optional arguments themselves) removed..

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      CellVal = UCase(SearchRange(X).Value)
      ReturnVal = ReturnRange(X).Value
      If CellVal = SearchString Then
        Result = Result & " " & ReturnVal
      End If
    Next
    
    LookUpConcat = Mid(Result, 2)
  End If
  
End Function

If you are sure you will always pass in single column and/or single row ranges, then we can remove the error testing code for those also, making the following code as stripped down as possible...

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  SearchString = UCase(SearchString)
  For X = 1 To SearchRange.Count
    CellVal = UCase(SearchRange(X).Value)
    ReturnVal = ReturnRange(X).Value
    If CellVal = SearchString Then
      Result = Result & " " & ReturnVal
    End If
  Next
  
  LookUpConcat = Mid(Result, 2)
  
End Function

 

[PCRIDE]

Thanks for supplying an alternate version of the code. I tried the second one as I will always be referring to the same column and it appears to be the same. Now that I have the dashboard completed, and I have the following code in 1 cell for each Service, it locks up Excel, the screen goes white transparent, its "Not Responding" then in about 15 seconds it updates. So the code works perfect, but there is something causing it to lock up and freeze as it runs through the code. BTW I am using both of your codes
CONCAT and LOOUPCONACT based on 2 criteria.

Here is my formula that refers to only 1 of the 10 services. Then each service will refer to its own column. (AI, AJ, AK, AL, AM, AN, AO, AP etc.. through BD columns)

=IF(E3="All Up",CONCAT(", ",Data!$AI$2:$AI$127),LookUpConcat('Service Dashboard'!$E$3,Data!$B:$B,Data!$AI:$AI))

When E3 = "All UP" the it changes instantly, so I know its not the first Logic, only when it runs the LookUpConcat is where i have the freezing.

 

[Rick Rothstein]

Thanks for supplying an alternate version of the code. I tried the second one as I will always be referring to the same column and it appears to be the same. Now that I have the dashboard completed, and I have the following code in 1 cell for each Service, it locks up Excel, the screen goes white transparent, its "Not Responding" then in about 15 seconds it updates. So the code works perfect, but there is something causing it to lock up and freeze as it runs through the code. BTW I am using both of your codes
CONCAT and LOOUPCONACT based on 2 criteria.

Here is my formula that refers to only 1 of the 10 services. Then each service will refer to its own column. (AI, AJ, AK, AL, AM, AN, AO, AP etc.. through BD columns)

=IF(E3="All Up",CONCAT(", ",Data!$AI$2:$AI$127),LookUpConcat('Service Dashboard'!$E$3,Data!$B:$B,Data!$AI:$AI))

When E3 = "All UP" the it changes instantly, so I know its not the first Logic, only when it runs the LookUpConcat is where i have the freezing.

How many of these LookUpConcat formulas do you have on the worksheet? The code is doing a lot, but I wouldn't think a 70x50 range would cause the kind of slow down you are reporting. Can you send me a copy of this "slow" workbook so I can trace through the code and see if I can speed it up? If so, my email address is..

rick DOT news AT verizon DOT net

Please mention this thread's title in the email message so I can more easily find my way back here when I have finished.

 

[PCRIDE]

HI Rick, I can't send the work book unfortunately but I ran a few tests. I currently have 23 cells that have this formula as I described above, but the look up tables are very small, even the 70x50 is not the entire look up table. That was referring to my entire data table.

I removed all the formula's and put it only in 1 cell, and it did the same thing, not as long but with 1 cell populated with the formula it takes 15 seconds of freezing and not responding before it updates, with all 23 cells populated with that formula it takes up to 45 seconds. The problem I have is I can't have the users of the dashboard wait 45 seconds each change.

I then commented out the code you gave me above, then it ran fine, no delay, it just returned errors for the Region views because obviously the code was commented out.

I'm thinking its the range you have defined in the code possibly? My knowledge with VBA is very vague so I can't be certain but is the code searching too much? For example

SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count


Can we limit this to an actual Range? or does the formula do that?

 

[Rick Rothstein]

I removed all the formula's and put it only in 1 cell, and it did the same thing, not as long but with 1 cell populated with the formula it takes 15 seconds of freezing and not responding before it updates, with all 23 cells populated with that formula it takes up to 45 seconds. The problem I have is I can't have the users of the dashboard wait 45 seconds each change.

I do not see how a single instance of my code could possibly take 15 seconds to execute... that just doesn't seem right. Is there anyway you can garble your data (I assume personal information is the reason you can't send me a copy of your workbook) and then send that to me? Without being able to see first hand what is happening as my code executes, I don't see how I can debug the problem

Can we limit this to an actual Range? or does the formula do that?

The arguments you pass into the function limit the range over which it works.

 

[PCRIDE]

So I replicated the workbook to some extent without replicating 1 other tab which has other formula's for a second dashboard, I also have image look ups, which pulls branding based on the Account and as I mentioned SQL connection to sharepoint which could be conflicting somehow, but again I build a lot of these dashboards and know excel's limits and I feel I am far from hitting any limitation of excel and fairly confident there is no conflicting items, but then again you never know.

Something tells me its the data connection but who knows...

The replicated workbook seems to work fine with no freezing or lagging.

Could be corruption as well.

Thanks for helping however AND the code which helps me greatly. I'll test it on other machines/versions as well.

I'll post back any changes.