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

 

[kyleno]

This is for Excel 2013 btw.

 

[Joe4]

Your formula is already long and cumbersome, and you want to add more conditions on it.
l would recommend creating your function in VBA to do what you want, so it isn't quite so long and cumbersome (and will be easier to maintain).

 

[kyleno]

I know it is... I wanted the cell to change when data is entered or removed without having to run any scripts. This formula does it quite well actually.

 

[Joe4]

This formula does it quite well actually.

Your question highlights the problem with these type of formulas. Because they are so long and unwieldy, they are not easy to modify if you want to change anything.

I wanted the cell to change when data is entered or removed without having to run any scripts.

What I am talking about does not involve having to run any scripts.
I am talking about creating your own function in VBA that does what you want. You can then use that function like any other native Excel function.
Something like shown here: https://www.wikihow.com/Create-a-User-Defined-Function-in-Microsoft-Excel

One other advantage is that it looks like you are applying this on a contiguous range of BE2:BL2. You could use a loop to loop through the range so you don't need to explicitly right a calculation for each and every cell.

 

[kyleno]

I have used the User Defined Functions before and they do work great. The problem I have is that I have absolutely no idea where to begin. I don't know anything about VBA coding at all. I checked out the WikiHow link and was completely confused. But, I agree that a user defined function would probably be a lot better than a long complicated formula.

 

[Joe4]

I can try to give you a hand, but would like it if you could explain exactly what you want this formula to do. I was trying to reverse engineer your formula, but can't say I quite follow all the logic/reasoning behind it.
So if you could just explain all that it should do and provide a few examples, I will see what I can come up with.

 

[kyleno]

Sure thing. I actually have two formulas I'm trying to work out.

1) The first formula will live update as text is inserted into a cell. It will concatenate text from a range, BUT will NOT add an orphan comma at the end if there is not text in fields after the last cell of text.

Ex.
Range A:G

A| B| C| D| E| F| G|
Hi| Low| Fast| | | | |

Result:
Hi, Low, Fast

NOT:
Hi, Low, Fast,,,,

Purpose:
Names will be added or removed from cells within the range and I need a combined list automatically created.

Below is the formula I am using to achieve this.

=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<>""))))

Note: Currently my range is actually 39 cells and will likely increase.


2) I attempted to reuse formula 1 above, but on a much smaller range of 8 columns. The issue that I am dealing with now is that there are cells that appear to have no text but actually contain the following formula to indicate the existence of text in a range:

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

So the concatenated cell appears as "Hi, Low, Fast,,,," with the orphan commas at the end.


It would be more import to me to have the first formula work than the second.

Does this help?

 

[Joe4]

Try this. It should handle both your situations:

Function MyConcat(myRange As Range) As String

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

So, to use it on a range like BE2 to BL2, just enter this formula in your cell:
=MyConcat(BE2:BL2)

 

[kyleno]

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.

Ex:
1) Low,
2) Hi, Low, Fast,