Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Help me transposing multiple rows into one column with row labeling in Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 17-01-2012
Member
 
Join Date: Jan 2012
Posts: 17
Help me transposing multiple rows into one column with row labeling in Excel

Dear all,I need to convert the following table:
a 1 2 3
b 7 8
c 4 5 6 9

Into:
a 1
a 2
a 3
b 7
b 8
c 4
c 5
c 6
c 9

how to do it? Help appreciated.

Reply With Quote
  #2  
Old 17-01-2012
Member
 
Join Date: Feb 2009
Posts: 156
Re: Help me transposing multiple rows into one column with row labeling in Excel

You can simply do it using Macros. Try out these Macros:
Sub ReOrganize()
'JBeaucaire (11/4/2009)
'Turns row data into columnar data
Dim LR As Long, i As Long, r As Long, c As Long, v As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row
i = 1

Do Until Range("A" & i) = ""
If Range("C" & i) <> "" Then
c = Cells(i, Columns.Count).End(xlToLeft).Column
v = i
For r = 3 To c
i = i + 1
Rows(i).Insert xlShiftDown
Range("A" & i) = Range("A" & i - 1)
Range("B" & i) = Cells(v, r)
Next r
End If
i = i + 1
Loop

Range("C1", Cells(Rows.Count, Columns.Count)).ClearContents
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #3  
Old 17-01-2012
Member
 
Join Date: Jan 2012
Posts: 17
Re: Help me transposing multiple rows into one column with row labeling in Excel

Thanks a lot for the above solution! It works well.

I also wanted to know Is it possible to put transposed data in a new sheet?
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Help me transposing multiple rows into one column with row labeling in Excel"
Thread Thread Starter Forum Replies Last Post
Cannot highlight distinct cells in different rows and column in a Pivot table ? Grayson Windows Software 3 27-11-2010 05:25 AM
Multiple rows for same id on Oracle 10 KADIN Software Development 4 23-11-2009 05:20 PM
How to Insert Multiple Rows At Once in PHP Zeverto Software Development 3 22-09-2009 03:30 PM
Merging duplicate names not rows copy rows into new workbook each time VBA Excel 2003 awkwardsmile Software Development 2 09-07-2009 08:57 AM
Auto hide rows and column in Excel 2007 Murena Windows Software 3 28-02-2009 07:00 PM


All times are GMT +5.5. The time now is 12:09 AM.