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

[shrinivasmj]

this is formula need to modify

=TRIM(IF(LEN(M311),"",M$1&", ")&(IF(LEN(N311),"",N$1&", "))

I need to remove last comma and add last sentence, if only one blanks cell is there add- ( is not available )

if more than 2 blanks cell need to add at last ( are not available )

 

[AlphaFrog]

Try something like this...

=IF(COUNTBLANK(M311:N311),

CHOOSE(COUNTBLANK(M311:N311),IF(LEN(M311),N1,M1)&" is",M1&" and "&N1&" are")&
​

" not available","")​

 

[shrinivasmj]

HI ITS WORKING BUT NEED FOR all the other fields in between like a2 to l2 and not take d and e row with same above formula i need

[TABLE="width: 985"]
<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]Phase (R/Y/B)[/TD]
[TD]Meter Make[/TD]
[TD]Meter Sl. No.[/TD]
[TD]Metre Type[/TD]
[TD]Meter Model[/TD]
[TD]Meter Mfg. Year[/TD]
[TD]Current Rating ()Amp)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10000160985[/TD]
[TD]+919845898986[/TD]
[TD]UG[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]LT-LTD[/TD]
[TD]JQ21518[/TD]
[TD]EM[/TD]
[TD]EM101[/TD]
[TD]1997[/TD]
[TD]5-20[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="5"><col><col span="3"></colgroup>[/TABLE]