Transpose
-
Hi! Expert I am not so good in Macros. I have write a macro a few month before for a excel file. Now i receive same type of file but more Columns and rows. Attached file:http://www.savefile.com/files/1788469 but unfortunately it will not work on this worksheets.there is 2 sheet in this file (Master and iwant this) i want to transpose some cells into Rows. i have a data about 9000 In Below mentioned Example: You see there is one Custcode Repeated 4 times with 1 Network and Prop Name but Four Childname and their ChildDOB in the Column CustCode networkname PropName ChildrenName Child DOB 221000000133 Jind Cable Network Ravinder Kumar Swati 8-Mar-91 221000000133 Jind Cable Network Ravinder Kumar Meenakshi 9-Apr-95 221000000133 Jind Cable Network Ravinder Kumar Shubham 16-Dec-96 221000000133 Jind Cable Network Ravinder Kumar Atul 17-Jul-98 What I want (NOTE THIS IS NOT A COMPLETE HEADERS DATA I HAD HIDE SOME COLUMNS BECAUSE FOR BETTER CLARIFICATION) Now, you can seen the difference between these two data specially the Childname and ChildDOB in 1st they both in columns but in 2nd one they both are in Row. CustCode networkname PropName ChildrenName Child DOB ChildrenName Child DOB ChildrenName Child DOB ChildrenName Child DOB 221000000133 Jind Cable Network Ravinder Kumar Swati 8-Mar-91 Meenakshi 9-Apr-95 Shubham 16-Dec-96 Atul 17-Jul-98 Now take a 2nd example: You see there is one Custcode Repeated 5 times with 1 Network But 2 Prop Name first one i.e Arjun Sharma have 3 Childname and with their ChildDOB ,But the second one i.e have 2 Childname and with their ChildDob. inColumns. 221000000232 City Communication Arjun Sharma Rajiv Sharma 10-Jul-79 221000000232 City Communication Arjun Sharma Suveta Sharma 13-Jan-81 221000000232 City Communication Arjun Sharma Seema Shurma 29-Oct-85 221000000232 City Communication Parshotam Lal Bhalla Kuldeep Bhalla 7-Sep-03 221000000232 City Communication Parshotam Lal Bhalla Isha Bhalla 24-Apr-05 What I want: 221000000232 City Communication Arjun Sharma Rajiv Sharma 10-Jul-79 Suveta Sharma 13-Jan-81 Seema Shurma 29-Oct-85 221000000232 City Communication Parshotam Lal Bhalla Kuldeep Bhalla 7-Sep-0
-
Hi! Expert I am not so good in Macros. I have write a macro a few month before for a excel file. Now i receive same type of file but more Columns and rows. Attached file:http://www.savefile.com/files/1788469 but unfortunately it will not work on this worksheets.there is 2 sheet in this file (Master and iwant this) i want to transpose some cells into Rows. i have a data about 9000 In Below mentioned Example: You see there is one Custcode Repeated 4 times with 1 Network and Prop Name but Four Childname and their ChildDOB in the Column CustCode networkname PropName ChildrenName Child DOB 221000000133 Jind Cable Network Ravinder Kumar Swati 8-Mar-91 221000000133 Jind Cable Network Ravinder Kumar Meenakshi 9-Apr-95 221000000133 Jind Cable Network Ravinder Kumar Shubham 16-Dec-96 221000000133 Jind Cable Network Ravinder Kumar Atul 17-Jul-98 What I want (NOTE THIS IS NOT A COMPLETE HEADERS DATA I HAD HIDE SOME COLUMNS BECAUSE FOR BETTER CLARIFICATION) Now, you can seen the difference between these two data specially the Childname and ChildDOB in 1st they both in columns but in 2nd one they both are in Row. CustCode networkname PropName ChildrenName Child DOB ChildrenName Child DOB ChildrenName Child DOB ChildrenName Child DOB 221000000133 Jind Cable Network Ravinder Kumar Swati 8-Mar-91 Meenakshi 9-Apr-95 Shubham 16-Dec-96 Atul 17-Jul-98 Now take a 2nd example: You see there is one Custcode Repeated 5 times with 1 Network But 2 Prop Name first one i.e Arjun Sharma have 3 Childname and with their ChildDOB ,But the second one i.e have 2 Childname and with their ChildDob. inColumns. 221000000232 City Communication Arjun Sharma Rajiv Sharma 10-Jul-79 221000000232 City Communication Arjun Sharma Suveta Sharma 13-Jan-81 221000000232 City Communication Arjun Sharma Seema Shurma 29-Oct-85 221000000232 City Communication Parshotam Lal Bhalla Kuldeep Bhalla 7-Sep-03 221000000232 City Communication Parshotam Lal Bhalla Isha Bhalla 24-Apr-05 What I want: 221000000232 City Communication Arjun Sharma Rajiv Sharma 10-Jul-79 Suveta Sharma 13-Jan-81 Seema Shurma 29-Oct-85 221000000232 City Communication Parshotam Lal Bhalla Kuldeep Bhalla 7-Sep-0
Sorry For not mentioned the Macors Sub sameoldcalvin() ' ' Macro3 Macro ' Dim i As Long Dim SearchRange As Range Dim FindRow As Range Dim pp As String Range("G1").Select Start: Do ' Whole process loop counter = do it 9000 times i = i + 1 ActiveCell.Offset(1, 0).Range("A1").Select pp = ActiveCell.Value Set SearchRange = Range("G:G") Set FindRow = SearchRange.Find(pp, LookIn:=xlValues, lookat:=xlWhole) If FindRow.Row = ActiveCell.Row Then GoTo Start Else Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select ActiveCell.Offset(0, 4).Range("A1:B1").Select Selection.Cut ActiveCell.Offset(-1, 0).Range("A1").Select Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -2).Range("A1").Select 'delete ActiveCell.Rows("1:1").EntireRow.Select ActiveCell.Activate Selection.Delete Shift:=xlUp ActiveCell.Offset(-1, 3).Range("A1").Select End If Loop Until i = 9000 End Sub