Need help with live updating concatenate formula...

[kyleno]

Hi all,

I have a brilliant formula that I need assistance updating. It concatenates a range of cells and will not include blank cells. It will also not include training commas when there are blank cells.

I need help adjusting the formula so that it will not include "apparently blank cells", e.g. =IF(COUNTIF(O2:W2,"*"),"ABC Co.","").

My range is BE2:BL2

Current Formula:
=IF(RIGHT(TRIM(IF(BE2<>0,BE2&", ", "")&IF(BF2<>0,BF2&", ", "")&IF(BG2<>0,BG2&", ", "")&IF(BH2<>0,BH2&", ", "")&IF(BI2<>0,BI2&", ", "")&IF(BJ2<>0,BJ2&", ", "")&IF(BK2<>0,BK2&", ", "")&IF(BL2<>0,BL2&", ", "")),1)<>",",TRIM(IF(BE2<>0,BE2&", ", "")&IF(BF2<>0,BF2&", ", "")&IF(BG2<>0,BG2&", ", "")&IF(BH2<>0,BH2&", ", "")&IF(BI2<>0,BI2&", ", "")&IF(BJ2<>0,BJ2&", ", "")&IF(BK2<>0,BK2&", ", "")&IF(BL2<>0,BL2&", ", "")),SUBSTITUTE(TRIM(IF(BE2<>0,BE2&", ", "")&IF(BF2<>0,BF2&", ", "")&IF(BG2<>0,BG2&", ", "")&IF(BH2<>0,BH2&", ", "")&IF(BI2<>0,BI2&", ", "")&IF(BJ2<>0,BJ2&", ", "")&IF(BK2<>0,BK2&", ", "")&IF(BL2<>0,BL2&", ", "")),",","",SUMPRODUCT(--(BE2:BL2<>""))))


Please and thank you!

~ Ky

 

[Joe4]

It's almost there! The only issue is that if there is a single name in the range there is still a trailing comma. Same issue with the last name in a range containing a trailing comma.

No, it doesn't do that. It explicitly drops the comma after the last value (that is what the last "If" statement does).
If you are seeing that, it probably means that you actually have some cells which look empty, but in fact, are not empty. The have a single space (or spaces) in them.

This modification to the code should account for that, and not include entries which just have spaces.

Function MyConcat(myRange As Range) As String

    Dim cell As Range
    Dim myString As String
    
    For Each cell In myRange
        If [COLOR=#ff0000]Trim([/COLOR]cell.Value[COLOR=#ff0000])[/COLOR] <> "" Then myString = myString & cell.Value & ","
    Next cell
            
    If Len(myString) > 0 Then MyConcat = Left(myString, Len(myString) - 1)
    
End Function

 

[kyleno]

I figured it out.

Function MyConcat(myRange As Range) As String
    Dim cell As Range
    Dim myString As String
    
    For Each cell In myRange
        If Trim(cell.Value) <> "" Then myString = myString & cell.Value & [B]",  "[/B]
    Next cell
            
    If Len(myString) > 0 Then MyConcat = Left(myString, Len(myString) - [B]2[/B])
    
End Function

I added a space AFTER the comma on line 6 and that is adding the trailing comma and changed the character length in line 9 to -2.

I had initially only added the space after the comma to make the list appear cleaner. Then realized I needed to adjust the character length to 2.

 

[Joe4]

I added a space AFTER the comma on line 6 and that is adding the trailing comma and changed the character length in line 9 to -2.

Yep, that would do it!

You need to be careful when modifying code, the implications of what it will do, and where else you need to adjust for it!

 

[kyleno]

Is there anyway to use this same UDF in conjunction with a conditional formula like

=IF(COUNTIF(O2:W2,"*"),"ABC","")

which does not always show a value but will have a formula?

I know this is a tall order but I was hoping that it might be a possibility.

Thanks.

 

[Joe4]

I am not entirely sure what you are asking. Can you try explaining in more detail how you are trying to incorporate it?

 

[kyleno]

My apologies, I reapplied the UDF to the range that contains the

=IF(COUNTIF(O2:W2,"*"),"ABC","")

code and it works perfectly!

Thank you so much for your assistance and patience. It has been a tremendous help!

 

[Joe4]

You are welcome.