Like a pivot table

[Iqbal]

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

 

[Malik]

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

 

[Iqbal]

Thank you Malik for your response

first:

- since I'm first time sending to group, I do not know how to mark "yes" as
requested by you. Noting that I'm using outlook express if this info will
heop

'-----------------------

***If this information was helpful, please indicate this by clicking
''''Yes''''. ***

---------------------


second:-
Your solution still needs two major things
a- I had in column 1 repeated name and not as the same when run your code

b- Normally I have more than one table in single sheet. So if you can help
and base the table on selection ( I can select the table before running ) &
give me a chance to select top left destination cell

Thank you again for your help

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Consolidate multiple Tables,
transpose data into rows.
No code, no formulas needed.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_04_10a.xlsx
Pdf preview:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_04_10a.pdf
If this helped, smile.

 

[Iqbal]

Thank you Herbert

Realy this what I need but unfortunetly I try to use the consildate but I
fail.
I go to the help of Exel 2007 for consildate. I go to the Data menu- data
tools - consildate
I pick table1 range
then click the add
then pick tabl2
then click the add then
then pick the count function
then pick take informtaion from row labes
then click enter

No table with cosildated info, but cout of items which I even do not know
what it present.

Please give me more info to proeceed or at least where to go

Thanking you again

 

[Iqbal]

Thank you Herbert

I smiled

I go the microsoft site and find it.

If you have time, you may guide me how to do it without the wizard

Thank you again

 

[Herbert Seidenberg]

Excel 2007
Consolidate multiple Tables,
transpose data into rows.
Wizardless guide: Slowly step through the macro.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_04_10a.xlsm
http://www.mediafire.com/file/mydjimqtz5y/04_04_10b.pdf
Keep smiling.

 

[Iqbal]

Thank you Hebert

I can not open the excel file, but I have viewed it in PDF version

It will be helpful if I can open the excel file


Thanks again