How to merge to columns into one, putting them into every other cell?

[NTNU-trykk]

Hi there

We have a excel file with two columns: Usernames (A) and password (B). We need to merge/intertwine these columns into one, placing all the information into every other cell. Example:

-NOW-

Username	Password
ABC	123
ABC	123
ABC	123

-WE NEED-

User/password	*now blank*
ABC
123
ABC
123
ABC
123
ABC
123

The reason we need it like this is simply an old way to go about it as the client wants one sticker with the username and one with the password and they need to be after each other so they know what password goes to which username when they mail it.

In advance, thanks for your help!

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

[Mallycat]

Full marks to you NTNU-trykk for an excellent "model problem" post. Great description in the title, great example clearly outlined with before and after, and even an explanation as to why. If only all requests for help were so well communicated.

[Marcol]

A possibility without using VBa
Wrap this in an IF() statement if you want to avoid the formula returning zeros

Please Login or Register  to view this content.

so ....
In D2

Please Login or Register  to view this content.

Drag/Fill Down as required.

Then Copy > Paste Special > Values to your desired location.

[icestationzbra]

a couple of formulaic methods...

[ChemistB]

or this formula Assuming data in A2:B15
In C2 dragged down

=INDEX($A$2:$B$15,INT((ROW(A1)+1)/2), MOD(ROW(A2),2)+1)

[icestationzbra]

akin to CB's, another method that i found some expert using:

if your data are in A2:B4, start in any other column at any row:

=INDEX($A$2:$B$4, CEILING(ROW(A1)/2,1), MOD((ROW(A2)),2)+1)

[Marcol]

Or maybe just ...

Please Login or Register  to view this content.

And hence to avoid zeros or errors when the table runs out

Please Login or Register  to view this content.