If Formula not working as expected

[Svmaxcel]

Hi,
I have some column which contains feedback or comments about the interaction.
So, instead of going and reading every column, I want to combine them.
I used the below formula, but not all comments are coming.
Example.

If e2 and f2 are blank and j2and k2 has some data, its getting skipped.

Need your help in solving the issue.

=IF(E2="","",E2&" - ")&IF(F2="","",F2&" --- ")&IF(J2="","",J2&" - ")&IF(K2="","",K2&" --- "&IF(O2="","",O2&" - ")&IF(P2="","",P2&" --- ")&IF(T2="","",T2&" - ")&IF(U2="","",U2&" --- ")& IF(Y2="","",Y2&" - ")&IF(Z2="","",Z2&" --- ")&IF(AD2="","",AD2&" - ")&IF(AE2="","",AE2&" --- ")&IF(AI2="","",AI2&" - ")&IF(AJ2="","",AJ2&" --- ")&IF(AN2="","",AN2&" - ")&IF(AO2="","",AO2&" --- ")&IF(AS2="","",AS2&" - ")&IF(AT2="","",AT2&" --- ")&IF(AX2="","",AX2&" - ")&IF(AY2="","",AY2&" --- ")&IF(BC2="","",BC2&" - ")&IF(BD2="","",BD2&" --- ")&IF(BH2="","",BH2&" - ")&IF(BI2="","",BI2&" --- ")&IF(BM2="","",BM&" - ")&IF(BN2="","",BN2&" --- "))

[Bob Phillips]

Not being skipped here, it shows j2 and k2 with the suffix.

[offthelip]

there is an error in your if statement in the BM column: your are missing a 2


IF(BM2="","",BM&" - ")

[Svmaxcel]

Thanks a ton for this.
Wanted to know, if there is any limitation for using multiple if formula in single cell.
As you can see the above example, it can really become a difficult job for excel to get the comments for 1000+ cells.

[offthelip]

I would write my own UDF ( user defined function) to do this. this one will concatenate any selected range with comma in between. as you can see it is a lot easier than writing a compolicated if statement:

Function conc(rng As Range)
txt = "'"


For Each cell In rng
 txt = txt & cell.Value & ","
Next cell
conc = txt
End Function

to use the function use just like any other excel function :

=conc(A1:C3)

this concatenates all the cells in the range A1 to C3 with comma inbetween

[offthelip]

I have updated this to do exactly what you want , ( I think), it concatenates every 5 and 6 th column with alternating single dash and triple dashes

Function conc(rng As Range)
txt = "'"


For Each Cell In rng
 colno = Cell.Column
 remn = colno Mod 5
 If remn = 0 Then
 txt = txt & Cell.Value & "-"
 Else
   If remn = 1 Then
      txt = txt & Cell.Value & "---"
   End If
 End If
Next Cell
conc = txt
End Function