Pivottable? Help!

[REM7600]

Hi All,

I would like to convert the table below to one that resembles the one at the bottom... Anyone with any easy way to do this?

Thanks!

FROM THIS

F1 F2
12345 VA7X
12345 40-5
12345 M5
12345 CXT1
12346 50-3
12347 VM8X
12347 VM25C



TO THIS

F1 F2 F3 F4 F5
12345 VA7X 40-5 M5 CXT1
12346 50-3
12347 VM8X
12347 VM25C

 

[Dave Peterson]

Pivottables can show counts, sums, averages, and lots of numeric functions in
the data area. But it can't show Text.

Saved from a previous post:

Your data is already sorted????

If yes, how about a little macro:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim PrevKey As Variant

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

PrevKey = ""
With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
oCol = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, 1).Value = PrevKey Then
'keep adding to the right
newWks.Cells(oRow, oCol).Value = .Cells(iRow, 2).Value
oCol = oCol + 1
Else
'new key, go to next row
PrevKey = .Cells(iRow, 1).Value
oRow = oRow + 1
newWks.Cells(oRow, 1).Value = .Cells(iRow, 1).Value
newWks.Cells(oRow, 2).Value = .Cells(iRow, 2).Value
oCol = 3
End If
Next iRow
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

 

[REM7600]

Dave your solution worked well.

Thanks

Travis

Pivottables can show counts, sums, averages, and lots of numeric functions
in
the data area. But it can't show Text.

Saved from a previous post:

Your data is already sorted????

If yes, how about a little macro:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim PrevKey As Variant

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

PrevKey = ""
With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
oCol = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, 1).Value = PrevKey Then
'keep adding to the right
newWks.Cells(oRow, oCol).Value = .Cells(iRow, 2).Value
oCol = oCol + 1
Else
'new key, go to next row
PrevKey = .Cells(iRow, 1).Value
oRow = oRow + 1
newWks.Cells(oRow, 1).Value = .Cells(iRow, 1).Value
newWks.Cells(oRow, 2).Value = .Cells(iRow, 2).Value
oCol = 3
End If
Next iRow
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