2

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

  • @DaveRook Thank you for your comment. The Pivot table only count the number (shows only the count) instead of displaying the value (e.g. L5). The solution is fine with me as long as it is done in Excel. (I believe you are using the Developer tab > Visual Basic / Macros, right?) – user275517 Aug 7 '14 at 10:02
  • @DaveRook Thank you for your comment. I have updated the question explaining why pivot tables won't work. – user275517 Aug 7 '14 at 10:13
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

enter image description here

And Worksheet2 after I run the vba

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.