Transpose Column to Row

[Hardeep_kanwar]

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

 

[sameoldcalvin]

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")

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

 

[Hardeep_kanwar]

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

 

[Hardeep_kanwar]

Still waiting For your reply

hardeep kanwar

 

[Don Guillett]

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]

Have you tried mine?

 

[Hardeep_kanwar]

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]

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]

I copied the other code to your workbook and tested. Same result as mine but
very clumsy due to selections.

 

[Hardeep_kanwar]

(e-mail address removed)

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
(e-mail address removed)