Unmerge and fill down

**dahrius** · 02-17-2010, 06:36 PM

I have a sheet that has merged cells and two unmerged cells next to it. I want the merged cell to unmerge and repeat the info in the cell below. For instance, (as in the attached sheet) I want Category One to be repeated in A1 and A2 and Category 2 to be repeated in A3 A4.

**martindwilson** · 02-17-2010, 07:08 PM

1 unmerge cells
2,in c1 put =a1
3 in c2 put =IF(ISBLANK(A2),A1,A2)
4 drag down
5 then copy col C paste special/values over col A
6 delete col c

**rickyilas** · 02-18-2010, 03:53 AM

Hi Martin,

Just want to follow-up a question on this. What if there are random blank rows? How could you automatically fill in column C? This one is having just 1 blank rows alternately but sometimes there might be 2 or 3. Can it be done with a formula?

Thanks

**martindwilson** · 02-18-2010, 05:08 AM

same formula would apply. but you'd get loads of zeros
so use
in c1 =a1
in c2 =IF(A2="",IF(A1="","",A1),A2)

if you wanted to fill in all the gaps
then
in c1 =a1
in c2 =IF(A2="",C1,A2)