Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
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 "Malik" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
Excel 2007 PivotTable
Consolidate multiple Tables, transpose data into rows. No code, no formulas needed. http://c0718892.cdn.cloudfiles.racks...04_04_10a.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks.../04_04_10a.pdf If this helped, smile. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
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 "Herbert Seidenberg" wrote in message ... Excel 2007 PivotTable Consolidate multiple Tables, transpose data into rows. No code, no formulas needed. http://c0718892.cdn.cloudfiles.racks...04_04_10a.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks.../04_04_10a.pdf If this helped, smile. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
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" wrote in message ... Excel 2007 PivotTable Consolidate multiple Tables, transpose data into rows. No code, no formulas needed. http://c0718892.cdn.cloudfiles.racks...04_04_10a.xlsx Pdf preview: http://c0718892.cdn.cloudfiles.racks.../04_04_10a.pdf If this helped, smile. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
Excel 2007
Consolidate multiple Tables, transpose data into rows. Wizardless guide: Slowly step through the macro. http://c0718892.cdn.cloudfiles.racks...04_04_10a.xlsm http://www.mediafire.com/file/mydjimqtz5y/04_04_10b.pdf Keep smiling. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Like a pivot table
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 "Herbert Seidenberg" wrote in message ... Excel 2007 Consolidate multiple Tables, transpose data into rows. Wizardless guide: Slowly step through the macro. http://c0718892.cdn.cloudfiles.racks...04_04_10a.xlsm http://www.mediafire.com/file/mydjimqtz5y/04_04_10b.pdf Keep smiling. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table and Pivot Table dates are not in correct order | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |