Concatenating cells in Excel ignoring blank cells

Question

I've searched through the site but can't seem to see anything that fits my problem.

I have 7 columns

User ID Session1 session2 session3 session4 session5 session6

Within the sessions 1-6, there will be a P or A to represent whether the user was "present" or "Absent". Based on this master data, I have created a further 6 columns to the right of this will the same headings and input IF statements to say =IF(B2="P","",B$1) so that it will show the Session ID if the user was absent from that session.

I then need to have all the users absent sessions within one column and that needs to be sepearated by commas. What I can't work out is how to get this without have duplicate commas where the cells are blank.

Any ideas?

Thanks James

Solution

Pick up the StringConcat UDF given at the link C Pearson Concatenation page. Copy the code starting from Function StringConcat(Sep As String, ParamArray Args()) As Variant upto the end, and paste it in a general module in VBA. If you don't know how to do this, refer this link which explains it all.

This UDF will ignore blanks, and concatenate any given range with a separator/delimiter of your choice.

Your solution then simply becomes

=StringConcat(", ",H2:M2)

Let me know if something isn't clear.

Alternate Solution :

Alternatively, modify your formulae as follows.

In cell H2, instead of =IF(B2="P","",B$1) , enter the following formula

=IF(B2="A",B$1&", ","")

Copy this formula upto M2, and down as many rows as you want. Now, in cell N2, put the following formula:

=IF(LEN(H2&I2&J2&K2&L2&M2)>2,LEFT(H2&I2&J2&K2&L2&M2,LEN(H2&I2&J2&K2&L2&M2)-2),"")

Hope this helps.