Like a pivot table
Hi,
This is simple, stupid solution. This solution assumes that you are reading
data from Sheet 1 and displaying data in sheet 2. We can polish it to reduce
the range size etc.
Add one command button in your sheet and call this procedure
Option Explicit
Public Sub TransposeData()
' Assume Row1 have data header
Dim oHeaderRange As Excel.Range
Dim oRowRange As Excel.Range
Dim Row As Long
Dim RowCounter As Long
Dim HeadCounter As Long
Set oHeaderRange = ThisWorkbook.Sheets(1).Range("1:1")
' Now imagine all the below rows have Data
Set oRowRange = ThisWorkbook.Sheets(1).Range("2:65535")
' Imagine you want to copy in another sheet 2
ThisWorkbook.Sheets(2).Select
Selection.Clear
' Now start placing data in the 2nd sheet
For RowCounter = 1 To oRowRange.Rows.Count
' Is the Column 1 is empty the end of the process
If oRowRange.Cells(RowCounter, 1).Value = "" Or
IsEmpty(oRowRange.Cells(RowCounter, 1).Value) = True Then
Exit For
Else
For HeadCounter = 1 To oHeaderRange.Columns.Count
If oHeaderRange.Cells(1, HeadCounter).Value = "" Or
IsEmpty(oHeaderRange.Cells(1, HeadCounter).Value) = True Then
Exit For
Else
Row = Row + 1
ThisWorkbook.Sheets(2).Select
Range(Cells(Row, 1), Cells(Row, 1)) =
oHeaderRange.Cells(1, HeadCounter).Value
Range(Cells(Row, 2), Cells(Row, 2)) =
oRowRange.Cells(RowCounter, HeadCounter).Value
End If
Next HeadCounter
End If
Next RowCounter
Set oRowRange = Nothing
Set oHeaderRange = Nothing
End Sub
--
Malik
***If this information was helpful, please indicate this by clicking
''''Yes''''. ***
"Iqbal" wrote:
Hello every body
I'm first time requesting in this group, so I opologize in advance for any
mistakes or something annoying
I repeat what I have sent before 10 min because I see it unclear when it
goes to news group
If any one can help me
I'm working with data which most of it comes like a table with feilds as
columns and records as rows. I want it to be as many rows with each feild
an example
what is exist
name age Joining Date Tele
John 20 Jun-90 4321251
Iqbal 30 Jul-95 6583752
George 40 Sep-85 7843125
What I want
John age 20
John Joining Date Jun-90
John Tele 4321251
Iqbal age 30
Iqbal Joining Date Jul-95
Iqbal Tele 6583752
George age 40
George Joining Date Sep-85
George Tele 7843125
Tables are varying of number of rows and number of feilds
Thanks for all
.
|