Fariedx asks if there is a way to paste 100 single cells into 100 Merged Cells of varying sizes from 2 to 6 Merged Cells. After reviewing several possible options that just won't work, Bill then uses a Circular Reference Formula to pull the Data from the Range of Single Cells. Follow along with Episode #1558 to learn this method and to learn why the others just won't work.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1558: Paste to Merge Cells.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in from the Netherlands. Oh this is a tough one.
He has a hundred merged cells right now, right now I only have five but they're different sizes anywhere from 2 cells to 6 cells and he has 100 values that he wants to be able to copy and then paste into those merge cells. Well, that just is not going to work.
I tried a lot of different things.
If you just do a Ctrl V it's going to unmerge, right, so no, we definitely not want to do that.
I said, all right, what if we copy and then select the constants only so Ctrl G4 go to special choose the constants, click OK.
Now I've selected in theory only those 5 cells. I will try a paste, paste Special Values.
I even tried skip blanks but that goes the other way, that gets rid of the blanks over here not, the blanks over here and it's just destroying the data.
I even tried turning everything sideways and using center across selection instead of merge cells and that just didn't work.
Alright, so here is the solution that I came up with.
We're going to select the 5 cells here, or in this case 100 cells and we're going to select only the constants again. So Ctrl G Special Constant.
So now I only have 5 cells selected, then a formula =INDEX( of this range over here, press F4 and I want to grab the successive value.
So I'm going to count, there's a count in case some of this is text, well it is text. The count a of D$1:D1 alright.
That's one of those cool expanding ranges.
Close the other parentheses and then I'm going to Ctrl Enter to enter that similar formula in all of the cells that have values.
Alright, now when I initially did this I got a big circular reference warning and all 0.
So in order to make it work I guess I’m doing a circular reference on purpose here because I'm counting and the count includes the cells that I'm filling in.
We want to go to file, you can't see it, just off your screen Options and then Formulas and turn on Enable Iterative Calculation.
That really should say, hey I'm using circular references and I'm ok with that. And then click OK and then this starts to work.
Let me just show you if we go into Excel Options and Formulas and that's unchecked.
Alright, then we get circular reference warning and if when you initially enter these they all show up as zeros.
So that, kind of, shut me down until I said: “Oh wait, let's start on the circular reference”. And sure enough it started to work, there you go.
And of course if you had more data that you wanted to copy and paste one out it's simple.
Alright, so you have some cells here, let's just do F G H and that, and that.
So now we take those cells copy them, paste to this, let's call it a helper column and the merge cells will pick up that value.
So not an exact copy paste again, this is another reason why people say: “Oh, don't use merge cells because they're just evil”.
In this case you know there might be a good valid reason why they needed to do that, although it certainly becomes much more of a hassle.
Wait, I want to thank you for stopping by and till the next time for the netcast, MrExcel.
Learn Excel from MrExcel podcast episode 1558: Paste to Merge Cells.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today's question sent in from the Netherlands. Oh this is a tough one.
He has a hundred merged cells right now, right now I only have five but they're different sizes anywhere from 2 cells to 6 cells and he has 100 values that he wants to be able to copy and then paste into those merge cells. Well, that just is not going to work.
I tried a lot of different things.
If you just do a Ctrl V it's going to unmerge, right, so no, we definitely not want to do that.
I said, all right, what if we copy and then select the constants only so Ctrl G4 go to special choose the constants, click OK.
Now I've selected in theory only those 5 cells. I will try a paste, paste Special Values.
I even tried skip blanks but that goes the other way, that gets rid of the blanks over here not, the blanks over here and it's just destroying the data.
I even tried turning everything sideways and using center across selection instead of merge cells and that just didn't work.
Alright, so here is the solution that I came up with.
We're going to select the 5 cells here, or in this case 100 cells and we're going to select only the constants again. So Ctrl G Special Constant.
So now I only have 5 cells selected, then a formula =INDEX( of this range over here, press F4 and I want to grab the successive value.
So I'm going to count, there's a count in case some of this is text, well it is text. The count a of D$1:D1 alright.
That's one of those cool expanding ranges.
Close the other parentheses and then I'm going to Ctrl Enter to enter that similar formula in all of the cells that have values.
Alright, now when I initially did this I got a big circular reference warning and all 0.
So in order to make it work I guess I’m doing a circular reference on purpose here because I'm counting and the count includes the cells that I'm filling in.
We want to go to file, you can't see it, just off your screen Options and then Formulas and turn on Enable Iterative Calculation.
That really should say, hey I'm using circular references and I'm ok with that. And then click OK and then this starts to work.
Let me just show you if we go into Excel Options and Formulas and that's unchecked.
Alright, then we get circular reference warning and if when you initially enter these they all show up as zeros.
So that, kind of, shut me down until I said: “Oh wait, let's start on the circular reference”. And sure enough it started to work, there you go.
And of course if you had more data that you wanted to copy and paste one out it's simple.
Alright, so you have some cells here, let's just do F G H and that, and that.
So now we take those cells copy them, paste to this, let's call it a helper column and the merge cells will pick up that value.
So not an exact copy paste again, this is another reason why people say: “Oh, don't use merge cells because they're just evil”.
In this case you know there might be a good valid reason why they needed to do that, although it certainly becomes much more of a hassle.
Wait, I want to thank you for stopping by and till the next time for the netcast, MrExcel.
