CODEWITHSUNDEEP
excelexcel-formulavba
usert4jju7
usert4jju7

Reputation: 1813

Excel - Copy columns to rows

I have 3 columns in a sheet in excel as below

RAW DATA

I need the output in the below format on a SEPARATE SHEET

OUTPUT

I'm fine with either VB script or using just excel features. Could I please get some help?

Upvotes: 0

Views: 190

Answers (2)

teylyn
teylyn

Reputation: 35915

Try this macro. Place the macro in a regular code module (Insert > Module). Adjust the ranges to suit your situation.

Sub rearrange()
Dim cel As Range, tgt As Range

Set cel = ActiveSheet.Range("A1")
Set tgt = ActiveSheet.Range("D1")
Do While Len(cel) > 0
    tgt = cel
    tgt.Offset(1, 0) = cel.Offset(0, 1) & cel.Offset(0, 2)
    Set cel = cel.Offset(1, 0)
    Set tgt = tgt.Offset(2, 0)
Loop
ActiveSheet.Range("A:C").Delete

End Sub

Upvotes: 1

itsols
itsols

Reputation: 5582

If you're not going to do this on a regular basis, here's a simple solution.

I don't have access to MS-Excel so I cannot give you the exact answer. But I hope this helps.

Steps:

  1. Add a new column with the concatenate function to the right of the table for example, to merge cells b1 and c1, use =Concatenate(b1,c1) and keep this result in cell D1. Do a copy-paste of the function for the other rows as well.
  2. Copy your selection to a new worksheet where you want the result.
  3. Use paste special to only paste the values of copied cells without forumulas. This ensures that it won't reference the original cells or change relatively.
  4. Use the transpose function to change the resulting contents like your final output while pasting the data. Similar one here.

If you need to do this regularly, this method is not suitable. You'll be better off with a VBA script. But it's been a very long time since I worked on Excel so I cannot help you there.

Upvotes: 0

Related Questions