Horizontal data to be displayed in vertical

[vijay.vizzu]

Dear All,


I am a big fan of Chandoo.org. Thanks to chadoo to start this blog. i have learned so many things and tips from this site. All excel ninjas really awesome, but till now i am in confusion that how they can think like a machine. Really awesome,


Now coming to the point, i have an excel sheet(attached) which i am doing updation in daily basis. Now i have an requirement that (in sample format i have explained) the data which i maintains in main sheet is in vertical format, now i want to display some columns data in horizontal (Vendor Names to be traspose). Now i am doing manually, it takes so much time to prepare, becoz i have so many sheets like this.


http://sdrv.ms/OF9rCG


So please help to resolve my burden, any help would be highly appreicated.


Thanks

Vijay

 

[vijay.vizzu]

Dear all Excel Ninjas.... Please try to resolve my issue.. Plz..


Vijay

 

[NARAYANK991]

Hi Vijay ,


Can you check out this file ?


http://sdrv.ms/SN4naV


Please note that I have used the entire part no , whereas I think you have used a substring to denote the part no. Please let me know how many characters from the part no. need to be used to designate a part uniquely.


Please try out the formula in your entire worksheet so that we know whether calculations slow down with the formulae.


Narayan

 

[vijay.vizzu]

Dear Narayan,


Thank you so much for your reply. Formula works fine but it takes so much time to calculate and the it should considered, if part no starts with 9 then it should consider 11 characters including"-", otherwise it should consider 9 characters including"-". both are should be start from left. and one more thing vendor names should not be repeated for for single part no.


I hope you can understand


Thanks

Vijay

 

[Krishnakumar]

Hi Vijay,


Try this VBA.

[pre]

Sub kTest()

Dim ka, k, i As Long, d  As Object, Sht As Worksheet

With Worksheets("Main Data")
ka = .Range("j11:r" & .Range("j" & .Rows.Count).End(xlUp).Row).Value2
End With
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
With CreateObject("scripting.dictionary")
.comparemode = 1
For i = 1 To UBound(ka, 1)
If Not d.exists(ka(i, 1) & "|" & ka(i, 9)) Then
.Item(ka(i, 1) & "|" & ka(i, 8)) = _
IIf(Len(.Item(ka(i, 1) & "|" & ka(i, 8))), .Item(ka(i, 1) & "|" & ka(i, 8)) & "|" & ka(i, 9), _
ka(i, 1) & "|" & ka(i, 2) & "|" & ka(i, 8) & "|" & ka(i, 9))
d.Item(ka(i, 1) & "|" & ka(i, 9)) = Empty
End If
Next
k = .items
End With
If UBound(k) > 0 Then
On Error Resume Next
Set Sht = Worksheets("Results")
If Err.Number <> 0 Then
Set Sht = Worksheets.Add
Sht.Name = "Results"
End If
Err.Clear: On Error GoTo 0
With Sht
.UsedRange.Clear
.Range("a2").Resize(UBound(k) + 1) = Application.Transpose(k)
.Range("a2").Resize(UBound(k) + 1).TextToColumns .Range("A2"), 1, , , 1, , , , 1, "|", , , , 1
.Range("a1:c1") = Array("Part No.", "Part Name", "Person In charge")
For i = 4 To .Cells(1).CurrentRegion.Columns.Count
.Cells(1).Offset(, i - 1) = "Vendor " & i - 3
Next
.UsedRange.EntireColumn.AutoFit
End With
End If

End Sub

[/pre]

Kris