Pivot type behavior with text?
 

This is what I have:

Date Stock Code
1/1/05 MSFT A
1/1/05 IBM B
1/1/05 SUNW B
1/2/05 MSFT A
1/2/05 IBM A
1/2/05 SUNW B
.....
.....
3/9/05 SUNW A

Everyday, there will be the same N stocks.


I want:

Date MSFT IBM SUNW
1/1/05 A B B
1/2/05 A A B
.....
.....
3/9/05 B A A


If my field code was a neumeric value, I would use a pivot table and
sum on Code. This would work as there would be only one symbol per
day. However, as the field Code is Text, how would I do this?


Thanks.

How about a macro?

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim iCol As Long
Dim myInputRng As Range
Dim myCell As Range

Application.ScreenUpdating = False

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
Set myInputRng = .Range("a1:c" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Application.StatusBar = "determining headers"
With myInputRng
.Columns(2).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("a1"), Unique:=True
End With
End With

With newWks
With .Range("a:a")
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
Set myRng = .Range("a2", .Cells(.Rows.Count, "a").End(xlUp))
If myRng.Cells.Count 255 Then
MsgBox "too many stocks to fit on the worksheet!"
GoTo ExitNow:
End If
myRng.Copy
.Range("b1").PasteSpecial Transpose:=True
.Range("a:a").Clear
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

With curWks
Application.StatusBar = "Copying Dates"
With .Range("a:a")
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("a1"), Unique:=True
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
End With

With newWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("B2", .Cells(2, LastCol))

Application.StatusBar = "Populating lots of formulas"
For Each myCell In myRng.Cells
With myCell
.FormulaArray _
= "=INDEX(" & myInputRng.Columns(3).Address _
(external:=True, ReferenceStyle:=xlR1C1) & "," _
& "match(1,(" & myInputRng.Columns(1).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=rc1)*(" _
& myInputRng.Columns(2).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=r1c),0))"
End With
Next myCell

Application.StatusBar = "Filling the formulas down"
myRng.AutoFill _
Destination:=myRng.Resize(LastRow - 1)

Application.StatusBar = "Cleaning up"
With myRng.Resize(LastRow - 1)
.Value = .Value
.Replace what:="#n/a", replacement:="", lookat:=xlWhole, _
MatchCase:=False
.NumberFormat = "mm/dd/yyyy"
End With

Application.Goto .Range("a1"), scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

With .UsedRange
.Columns.AutoFit
End With
End With

ExitNow:
With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Essentially, this creates a list of unique dates and a list of unique stocks and
puts them on a new worksheet. (Down column A and across row 1.)

Then it fills in with a formula to return the first (and only match???) found
for each date/stock combination.




Jayesh Shah wrote:

This is what I have:

Date Stock Code
1/1/05 MSFT A
1/1/05 IBM B
1/1/05 SUNW B
1/2/05 MSFT A
1/2/05 IBM A
1/2/05 SUNW B
....
....
3/9/05 SUNW A

Everyday, there will be the same N stocks.

I want:

Date MSFT IBM SUNW
1/1/05 A B B
1/2/05 A A B
....
....
3/9/05 B A A

If my field code was a neumeric value, I would use a pivot table and
sum on Code. This would work as there would be only one symbol per
day. However, as the field Code is Text, how would I do this?

Thanks.

--

Dave Peterson