macro or formula to combine 2 cell (down) on a merged cell

[rampal]

hi all members,

having trouble in combining several cell-down into one cell on a sheet involving merged cell.

my attemp is using this formula [=IF(IsMerged(E7),D7&"-"&D8,""] but this is not giving the result I am hoping for.

to get the picture, please have a look on the attached sample workbook
samplerampal.xlsm

p.s: the borders on the attached worksbook does not nessecary, i just made it for easy viewing.
and my knowldge in formula and macro is way below basic

[ChemistB]

This formula will not merge the cells for you (you'll need a macro to do that) but will put all the values in the right places. I put it in G7 copied down

=IF(AND(IsMerged(B7),NOT(ISBLANK(B7))), A7 &"-"&A8 &IF(ISBLANK(B9), "-"&A9,"")& IF(AND(ISBLANK(B9), ISBLANK(B10)),"-"&A10,""),"")

[rampal]

hi ChemistB,

your formula works like charm and it solved my problem. (i just want to combine all the values in to one cell, so doesn't matter if cell is not merged)

a bunch of thanks to you .......!

regards
rampal

[samba_ravi]

Try this array formula

{=CHOOSE(SMALL(IF(LEN($B7:$B$24)>0,ROW($B7:$B$24),MAX(IF(LEN(A$7:A$24)>0,ROW(A$7:A$24),0))+1),2)-ROW(B7),A7,A7&"-"&A8,A7&"-"&A8&"-"&A9,A7&"-"&A8&"-"&A9&"-"&A10)}