|
Transpose Column to Row
Hi! Expert
http://www.savefile.com/files/1783586 I think is really Challenging I have data about 9000 and I want to Convert the data of Columns E: E and F: F into Rows. My problem is that in B: B there are Customer Code and in C: C is the Network name and in D: D is the Propname. There is only one DBCode against each NETWORKNAME. But one or more than one or it may go to 10 Propname. Now I want to Transpose data of E: E i.e. Child name into rows. Sheet 2 is the example of my question. Thanks in Advance Hardeep kanwar |
Transpose Column to Row
This macro should help you to convert it from master to what you
want...let me know :) Sub sameoldcalvin() ' ' Macro3 Macro ' Dim i As Long Dim SearchRange As Range Dim FindRow As Range Dim pp As String Range("D1").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("D:D") 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 |
Transpose Column to Row
Thanks for quick reply
it works in the example data but i run this macro in the original data it show the Errors. Because i am totally stupid in Macro. http://www.savefile.com/files/1785208 I am very sorry for not given the complete range of my row and Column. Pls find the original data of my question "sameoldcalvin" wrote: This macro should help you to convert it from master to what you want...let me know :) Sub sameoldcalvin() ' ' Macro3 Macro ' Dim i As Long Dim SearchRange As Range Dim FindRow As Range Dim pp As String Range("D1").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("D:D") 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 |
Transpose Column to Row
Still waiting For your reply
hardeep kanwar "sameoldcalvin" wrote: This macro should help you to convert it from master to what you want...let me know :) Sub sameoldcalvin() ' ' Macro3 Macro ' Dim i As Long Dim SearchRange As Range Dim FindRow As Range Dim pp As String Range("D1").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("D:D") 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 |
Transpose Column to Row
|
Transpose Column to Row
Your Code not working according to my requirement.
Sameolcalvin Macro is working good but some errors. I think with few changes in Sameoldcalvin Macros it is possible. 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-03 Isha Bhalla 24-Apr-05 You can see Difference Cleary. Specially Childname and ChildDOB. Any Help will be Appreciate Hardeep kanwar "Don Guillett" wrote: Try this idea Sub separatekidsBottomUp() lr = Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 mc = Application.CountIf(Columns(4), Cells(i, "d")) Select Case mc Case 1: x = 0 Case 2: x = 2 Case 3: x = 4 Case 4: x = 6 Case Else End Select If Cells(i, "d") = Cells(i - 1, "d") Then Cells(i - mc + 1, 5 + x) = Cells(i, 5) Cells(i - mc + 1, 6 + x) = Cells(i, 6) Rows(i).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Hi! Expert http://www.savefile.com/files/1783586 I think is really Challenging I have data about 9000 and I want to Convert the data of Columns E: E and F: F into Rows. My problem is that in B: B there are Customer Code and in C: C is the Network name and in D: D is the Propname. There is only one DBCode against each NETWORKNAME. But one or more than one or it may go to 10 Propname. Now I want to Transpose data of E: E i.e. Child name into rows. Sheet 2 is the example of my question. Thanks in Advance Hardeep kanwar |
Transpose Column to Row
My macro moves each child & dob to it's own column and deletes the old row. Deep Chand Dixit Gaurav Dixit 4-Sep-89 Garima Dixit 4-Sep-89 gaurav dixit 4-Sep-89 priya dixit 4-Sep-89 Send me your email addy and I'll send you your workbook modified to this. -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Your Code not working according to my requirement. Sameolcalvin Macro is working good but some errors. I think with few changes in Sameoldcalvin Macros it is possible. 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-03 Isha Bhalla 24-Apr-05 You can see Difference Cleary. Specially Childname and ChildDOB. Any Help will be Appreciate Hardeep kanwar "Don Guillett" wrote: Try this idea Sub separatekidsBottomUp() lr = Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 mc = Application.CountIf(Columns(4), Cells(i, "d")) Select Case mc Case 1: x = 0 Case 2: x = 2 Case 3: x = 4 Case 4: x = 6 Case Else End Select If Cells(i, "d") = Cells(i - 1, "d") Then Cells(i - mc + 1, 5 + x) = Cells(i, 5) Cells(i - mc + 1, 6 + x) = Cells(i, 6) Rows(i).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Hi! Expert http://www.savefile.com/files/1783586 I think is really Challenging I have data about 9000 and I want to Convert the data of Columns E: E and F: F into Rows. My problem is that in B: B there are Customer Code and in C: C is the Network name and in D: D is the Propname. There is only one DBCode against each NETWORKNAME. But one or more than one or it may go to 10 Propname. Now I want to Transpose data of E: E i.e. Child name into rows. Sheet 2 is the example of my question. Thanks in Advance Hardeep kanwar |
Transpose Column to Row
I copied the other code to your workbook and tested. Same result as mine but very clumsy due to selections. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... My macro moves each child & dob to it's own column and deletes the old row. Deep Chand Dixit Gaurav Dixit 4-Sep-89 Garima Dixit 4-Sep-89 gaurav dixit 4-Sep-89 priya dixit 4-Sep-89 Send me your email addy and I'll send you your workbook modified to this. -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Your Code not working according to my requirement. Sameolcalvin Macro is working good but some errors. I think with few changes in Sameoldcalvin Macros it is possible. 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-03 Isha Bhalla 24-Apr-05 You can see Difference Cleary. Specially Childname and ChildDOB. Any Help will be Appreciate Hardeep kanwar "Don Guillett" wrote: Try this idea Sub separatekidsBottomUp() lr = Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 mc = Application.CountIf(Columns(4), Cells(i, "d")) Select Case mc Case 1: x = 0 Case 2: x = 2 Case 3: x = 4 Case 4: x = 6 Case Else End Select If Cells(i, "d") = Cells(i - 1, "d") Then Cells(i - mc + 1, 5 + x) = Cells(i, 5) Cells(i - mc + 1, 6 + x) = Cells(i, 6) Rows(i).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Hi! Expert http://www.savefile.com/files/1783586 I think is really Challenging I have data about 9000 and I want to Convert the data of Columns E: E and F: F into Rows. My problem is that in B: B there are Customer Code and in C: C is the Network name and in D: D is the Propname. There is only one DBCode against each NETWORKNAME. But one or more than one or it may go to 10 Propname. Now I want to Transpose data of E: E i.e. Child name into rows. Sheet 2 is the example of my question. Thanks in Advance Hardeep kanwar |
Transpose Column to Row
"Don Guillett" wrote: My macro moves each child & dob to it's own column and deletes the old row. Deep Chand Dixit Gaurav Dixit 4-Sep-89 Garima Dixit 4-Sep-89 gaurav dixit 4-Sep-89 priya dixit 4-Sep-89 Send me your email addy and I'll send you your workbook modified to this. -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Your Code not working according to my requirement. Sameolcalvin Macro is working good but some errors. I think with few changes in Sameoldcalvin Macros it is possible. 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-03 Isha Bhalla 24-Apr-05 You can see Difference Cleary. Specially Childname and ChildDOB. Any Help will be Appreciate Hardeep kanwar "Don Guillett" wrote: Try this idea Sub separatekidsBottomUp() lr = Cells(Rows.Count, 1).End(xlUp).Row For i = lr To 2 Step -1 mc = Application.CountIf(Columns(4), Cells(i, "d")) Select Case mc Case 1: x = 0 Case 2: x = 2 Case 3: x = 4 Case 4: x = 6 Case Else End Select If Cells(i, "d") = Cells(i - 1, "d") Then Cells(i - mc + 1, 5 + x) = Cells(i, 5) Cells(i - mc + 1, 6 + x) = Cells(i, 6) Rows(i).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Hardeep_kanwar" wrote in message ... Hi! Expert http://www.savefile.com/files/1783586 I think is really Challenging I have data about 9000 and I want to Convert the data of Columns E: E and F: F into Rows. My problem is that in B: B there are Customer Code and in C: C is the Network name and in D: D is the Propname. There is only one DBCode against each NETWORKNAME. But one or more than one or it may go to 10 Propname. Now I want to Transpose data of E: E i.e. Child name into rows. Sheet 2 is the example of my question. Thanks in Advance Hardeep kanwar |
| All times are GMT +1. The time now is 06:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com