if find blank cell in coloum need to take the header name in remarks

[shrinivasmj]

[TABLE="width: 385"]
<colgroup><col></colgroup><tbody>[TR]
[TD]if find blank cell in coloum need to take the header name with comma after each word in remarks
[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 830"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Service Point No.[/TD]
[TD]Source No. (11 Digit Pole No.)[/TD]
[TD]Mobile / Landline No.[/TD]
[TD]Service Main (UG/OH)[/TD]
[TD]Supply (1/3)[/TD]
[TD]REMARKS[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]10000160985[/TD]
[TD] [/TD]
[TD]UG[/TD]
[TD]1[/TD]
[TD]Service Point No. ,Mobile / Landline No. ,[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]9845612589[/TD]
[TD]UG[/TD]
[TD]1[/TD]
[TD]Source No. (11 Digit Pole No.)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]10000160985[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]Service Point No. , Mobile / Landline No. , Service Main (UG/OH)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

How about?

=TRIM(IF(LEN(A2),"",A$1&", ")&IF(LEN(B2),"",B$1&", ")&IF(LEN(C2),"",C$1&", ")&IF(LEN(D2),"",D$1&", ")&IF(LEN(E2),"",E$1&", "))

 

[shrinivasmj]

hi need one more step to add here need to modify the formula

=TRIM(IF(LEN(A2),"",A$1&", ")&IF(LEN(B2),"",B$1&", ")&IF(LEN(C2),"",C$1&", ")&IF(LEN(D2),"",D$1&", ")&IF(LEN(E2),"",E$1&", "))

in remarks its need to remove the last comma and add the words ( is not available ) if single blank cell found ,

if more then one blank cell then add at last words as ( are not available )

 

[Andrew Poulsom]

Here you are:

=SUBSTITUTE(TRIM(IF(LEN(A2),"",A$1&", ")&IF(LEN(B2),"",B$1&", ")&IF(LEN(C2),"",C$1&", ")&IF(LEN(D2),"",D$1&", ")&IF(LEN(E2),"",E$1&", ")),",","",COUNTBLANK(A2:E2))&IF(COUNTBLANK(A2:E2)=1," is "," are ")&" not available"