PDA

View Full Version : If Formula not working as expected



Svmaxcel
08-23-2017, 08:57 AM
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
08-23-2017, 09:06 AM
Not being skipped here, it shows j2 and k2 with the suffix.

offthelip
08-23-2017, 09:27 AM
there is an error in your if statement in the BM column: your are missing a 2


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

Svmaxcel
08-24-2017, 12:17 AM
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
08-24-2017, 01:22 AM
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
08-24-2017, 01:40 AM
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