Translate Excel data rows data to a report table

Question

I have a excel file that contain the following data

Name  |  Items Sold  | Selling Price
=====================================
jeff  |  red pen     | L5
june  |  blue pen    | L6
jane  |  green pen   | L5
jeff  |  blue pen    | L5
june  |  green pen   | L7
jane  |  red pen     | L5

into this:

      | red pen  | blue pen  | green pen
==========================================
jeff  | L5       | L5        | -
june  | -        | L6        | L7
jane  | L5       | -         | L5

The 'L' follow by a digit refer to a selling price range. (e.g. L5 can be price from $0.4 to $0.5)

May I know how do I go about doing it as I tried Pivot table but does not get what I wanted.

Why Pivot Table does not work is because:

When I insert a Pivot table, I select the Name as the 'Row Labels', the Item Sold as 'Column Labels' and the Selling Price as 'Values', Excel will give me Count of Selling Price as 'Values'. There is no way for me to put it as normal plain values just the table that I wanted.

Below is what the Pivot Table will give me:

     Row Labels | blue pen | green pen  | red pen  | Grand Total
==================================================================
          june  | 1        | 1          |          | 2
          jane  |          | 1          | 1        | 2
          jeff  | 1        |            | 1        | 2
==================================================================
Grand Total     | 2        | 2          | 2        | 6

Answer 1

This VBa should do it (or at least, get you started)

Option Explicit

Sub myShaefferPenNibBroke()

'first, let's set up the headings on worksheet 2 and clear it all

Worksheets("Sheet2").Cells.Clear

Worksheets("Sheet2").Range("B1").Value = "red pen"
Worksheets("Sheet2").Range("C1").Value = "blue pen"
Worksheets("Sheet2").Range("D1").Value = "green pen"


Dim row As Integer
row = 2

Do While (Worksheets("Sheet1").Range("A" & row).Value <> "")

    Dim name As String
    name = Worksheets("Sheet1").Range("A" & row).Value

    Dim pen As String
    pen = Worksheets("Sheet1").Range("B" & row).Value

    Dim price As String
    price = Worksheets("Sheet1").Range("C" & row).Value

    Call UpdateOtherSheet(name, pen, price)

row = row + 1

Loop

End Sub

Sub UpdateOtherSheet(name As String, pen As String, price As String)

Dim row As Integer
row = 2

Dim doesExist As Boolean
doesExist = False

Do While (Worksheets("Sheet2").Range("A" & row).Value <> "")

    Dim resultName As String
    resultName = Worksheets("Sheet2").Range("A" & row).Value

    If (name = resultName) Then
       Call AddPrice(row, pen, price)
       doesExist = True
    End If



row = row + 1
Loop

 If Not doesExist Then
        row = 2
        Do While (Worksheets("Sheet2").Range("A" & row).Value <> "")
            row = row + 1
        Loop

        Worksheets("Sheet2").Range("A" & row).Value = name
        Call AddPrice(row, pen, price)

    End If

End Sub

Sub AddPrice(row As Integer, pen As String, price As String)

         Select Case pen
            Case "red pen"
                Worksheets("Sheet2").Range("B" & row).Value = price
            Case "blue pen"
                Worksheets("Sheet2").Range("C" & row).Value = price
            Case "green pen"
                Worksheets("Sheet2").Range("D" & row).Value = price
        End Select
End Sub

It reads the original table on worksheet1 and converts it all on workseet2

Worksheet 1

And Worksheet2 after I run the vba