Transpose Data

[Anbu]

Dear Friends,

I am new to this Forum. Expecting some excel help from you.

I need to change the rowwise data to columnwise data based on heading. Please help.

Example:

A1:Name: A
A2:Roll No.: 1
A3:Subject: English
A4:Name: B
A5:Roll No.:2
A6:Subject: Maths
A7:Mark: 80
A8:Name: C
A9:Roll No.:3
A10:Subject: Science
A11:Percentage: 60%




Thanks in advance.

Regards,
Anbu

 

[bobhc]

Expecting some excel help from you.

That's something I have all ways wanted clarify, just what is this forum for!!!

Good day Anbu
This link will help in the use of the forum site.

http://chandoo.org/forum/threads/new-users-please-read.294/

As to your question have you tried text to columns, you have multiple text qualifiers which will mean a bit more work, it would have been straight forward if you had kept to a : with no period.


.

 

[shrivallabha]

Please try this code on a backup copy.

I have prepared a basic code which will read data from range A2:B(n) and then paste resultant data in Column D1. The code is commented so it should be easier to follow.

Option Explicit
Public Sub DataArrange()
Const strHeader As String = "Name;Roll No.;Subject;Mark;Percentage"
Dim varRng As Variant, varHeader As Variant, varResult() As Variant
Dim lngLastRow As Long, i As Long, j As Long, k As Long

'\\ Split the Header and get it as Individual Heading
varHeader = Split(strHeader, ";")
'\\ Assuming data range is in A2:Bn. Adjust this to suit
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
varRng = Range("A2:B" & lngLastRow).Value
'\\ Change 10000 to suitable value if you have more rows
ReDim varResult(10000, UBound(varHeader))

'\\ Load header row in the result array
j = 0
For i = LBound(varHeader) To UBound(varHeader)
  varResult(j, i) = varHeader(i)
Next i

'\\ Process results
For i = LBound(varRng) To UBound(varRng)
  '\\ The first data in the header is used as marker to increment data
  If varRng(i, 1) = varHeader(0) Then
  j = j + 1
  End If
  '\\ Check and load data in appropriate columns
  For k = LBound(varHeader) To UBound(varHeader)
  If varRng(i, 1) = varHeader(k) Then
  varResult(j, k) = varRng(i, 2)
  End If
  Next k
Next i

'\\ Post data in the range D1. Change this to suit
Range("D1").Resize(j + 1, UBound(varHeader) + 1).Value = varResult

End Sub