How do I "merge" columns in Excel? By "merge" I don't mean CONCATENATE, but kind of like link them together.

Question

For example, A1="Hello", B1="World", the end result should be A1="Hello", A2="World". I am not talking about just several columns with limited rows (I can do manual then), here I have hundreds columns with different number of rows. Is there a fomula to do it? Or Macro? TIA.

Answer 1

Let's say you have columns A and B, and you want to put the results in C.

If your data starts at $A$1, you would have this formula in C1:

=OFFSET($A$1, INT((ROW()-ROW($A$1))/2), MOD(ROW()+1,2))

Copy and paste this down column C.

This can be expanded to work on more than 2 columns by changing these numbers:
"/2" -- change the 2 to the number of columns you're merging. e.g. if you're merging A through D, make this 4
"+1" -- should 1 less than the number of columns being merged. if merging A through D, make this 3 (if you're starting at an even row, e.g. $A$2 rather than $A$1, this should 2 less than the number of columns)
",2)" -- again, should be the number of columns being merged, if merging A through D, make this 4

If you have any questions, add more details or e-mail.
Good luck.
.

Answer 2

I don't think it does. Cleave means to split or cut. A cleavage bra may "boost ur bust" but the word cleavage is used in the sense of the gap or split between the breasts.

Answer 3

I'm not clear on your question - but let me give you an example of a file I recently worked on:

to keep firstname lastname in separate columns (for sorting purposes), yet produce a printed list with lastname,firstname,
I used:

------A--------B-------------C
Lastname--Firstname--Name
Doe ----------John -----------=A2&", "&B2

I added column C for Name.
In C2 I entered the formula you see
it yields------Doe, John

I dragged the formula to end-of-file.