CODEWITHSUNDEEP
exceltransposevba
Paul
Paul

Reputation: 23

Transposing 2 Cell Rows into 2 Cell Columns Quickly

Before-After Picture

I have been trying to quickly push two cells into subsequent columns. I tried transpose shortcuts, Text to Columns, and other coding, but the fastest way is Ctrl-X, Ctrl-V over and over again.

Does anyone have a quick/simple solution?

Upvotes: 2

Views: 72

Answers (3)

Am Novice
Am Novice

Reputation: 325

You can also use Kutools for performing operations such as Transposing a range in case you are not aware of formulas .

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152525

A quick formula:

(Note this will not delete the existing but merely copy over the values.)

Put this in column B next to the first name and copy over one column and down:

=IF(MOD(ROW(1:1)-1,4)=0,INDEX($A:$A,ROW()+COLUMN(A:A)),"")

Upvotes: 2

user4039065
user4039065

Reputation:

Some quick VBA would look like this,

dim rw as long
with activesheet
    for rw=315 to .cells(rows.count, "A").end(xlup).row step 4
        .cells(rw, "A").offset(-1, 1).resize(1, 2) = application.transpose(.cells(rw, "A").resize(2, 1)).value
        .cells(rw, "A").resize(2, 1).clearcontents
    next rw
end with

Step through a few loops with [F8] and when you are happy with it, put the cursor on End With and tap [ctrl]+[F8].

Upvotes: 3

Related Questions