Need help combining cells....PLEASE!!!

Posted by chevy4u2c on May 03, 2001 12:51 PM

So what I have is cells A1 through A1000, each with a number. I want to combine A1 and A2 into B1 and seperate with a comma. I've got that formula down OK; =A1&","&A2 . Now the next two cells I want to combine are A3 and A4, and I want to put them into B2.
I can't just Fill the formula down, because then it will combine A2 and A3. How can I have Excel combine cells in the order that I want them to, without me having to edit the formula everytime? Thanks for your help!

Posted by Mark W. on May 03, 2001 1:27 PM

1. Enter =A1&","&A2 into cell B1.
2. Select cell B1:B2.
3. Double-click the fill handle, the little black
square in the lower right-hand corner of cell B2
4. Perform an Edit | Go To... | Special... Blanks.
5. Perform an Edit | Delete..., click the
"Shift cells up" radio button, and press [ OK ].

Posted by Aladin Akyurek on May 03, 2001 1:32 PM

The way I understand your problem, you want:

=IF(MOD(ROW(),2)=0,"",A1&","&A2)

Aladin

Posted by Mark W. on May 03, 2001 1:37 PM

So much better than mine!

Posted by Mark W. on May 03, 2001 1:40 PM

But...

Need to get the 3,4 results into cell B2.

Posted by chevy4u2c on May 03, 2001 1:53 PM

Thanks Aladin, but now how do I get rid of the blanks?

Posted by chevy4u2c on May 03, 2001 2:00 PM

Thanks for your help too, Mark!

Posted by Aladin Akyurek on May 03, 2001 2:11 PM

That's exactly Mark's "But..."

Looking at...

Posted by Mark W. on May 03, 2001 2:21 PM

Aladin, chevy4u2c... Check this out!

Enter...

=OFFSET(A1,ROW()-1,) & IF(ISBLANK(OFFSET(A1,ROW()-1,)),"",",") & OFFSET(A2,ROW()-1,)

into cell B1 and copy down.

Posted by Aladin Akyurek on May 03, 2001 2:30 PM

Re: That's exactly Mark's "But..."

Here we go:

In B1 enter: =IF(ROW() <= COUNT(A:A)/2,A1&","&A2,"")
In B2 enter: =IF(ROW() <= COUNT(A:A)/2, INDIRECT(ADDRESS(ROW() + COUNT($A$1:A1),1)) & "," & INDIRECT(ADDRESS(ROW() + COUNT($A$1:A1)+1,1)),"")

Copy down the latter as far as needed.

Aladin

Posted by Aladin Akyurek on May 03, 2001 2:36 PM

Re: Aladin, chevy4u2c... Check this out!

That works too.

Chevy4u2c -- Mark's is much shorter, so use his.

Aladin

Posted by IML on May 03, 2001 3:00 PM

Can we please leave Mark's but out of this?

Posted by chevy4u2c on May 03, 2001 3:05 PM

WOW!!! That works great! Thanks to Aladin, Mark W., and ....Mark's but!

Posted by Mark W. on May 03, 2001 3:12 PM

Hey! Keep my but out of this! : )