Pivot Table

[mirrorwatch]

Based on the data below, now I want to have a table or even pivot table, where I can know if the items are 1 or nothing.

1)The effect 1 represents 1.
2)The effect -1 represents nothing(blank cell)
3)And if a single item appear twice, and the sum(eg.1+(-1)) add up to 0, it represents nothing again.

1)Similarly, if I request for Watch, I should get 1
2)So if I request for Book, I should have an empty cell
3)And if request for Paper, I should get empty cell.

And a single item at max can only appear twice

The problem is there are like thousands items list, so I need a vba/macro for it?




<table style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="FONT-WEIGHT: bold; WIDTH: 30px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody> <tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"> <td>
</td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</td> <td style="BACKGROUND-COLOR: #99cc00">Items</td> <td style="BACKGROUND-COLOR: #99cc00">Effect</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</td> <td>Apple</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</td> <td>Orange</td> <td style="TEXT-ALIGN: right">-1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</td> <td>Mouse</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td> <td>Book</td> <td style="TEXT-ALIGN: right">-1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td> <td>Apple</td> <td style="TEXT-ALIGN: right">-1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</td> <td>Watch</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</td> <td>Paper</td> <td style="TEXT-ALIGN: right">-1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</td> <td>Paper</td> <td style="TEXT-ALIGN: right">1</td></tr></tbody></table>

 

[mirrorwatch]

Try this macro:

Sub Test()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        ActiveSheet.Range("A1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    With ActiveSheet.PivotTables(1)
        .AddFields RowFields:="Items"
        With .PivotFields("Effect")
            .Orientation = xlDataField
            .NumberFormat = """P"";""N"";"""""
        End With
    End With
End Sub

Sorry but that it not completely work,

Firstly, P represents positive and N represents negative.

I want

<table border="1" cellpadding="0" cellspacing="0"><tbody><tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><td>
</td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</td> <td style="BACKGROUND-COLOR: #99cc00">Items</td> <td style="BACKGROUND-COLOR: #99cc00">Effect</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</td> <td>Apple</td> <td style="TEXT-ALIGN: right">P</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</td> <td>Orange</td> <td style="TEXT-ALIGN: right">N</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</td> <td>Mouse</td> <td style="TEXT-ALIGN: right">P</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td> <td>Book</td> <td style="TEXT-ALIGN: right">N</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td> <td>Apple</td> <td style="TEXT-ALIGN: right">N
</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</td> <td>Watch</td> <td style="TEXT-ALIGN: right">P</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</td> <td>Paper</td> <td style="TEXT-ALIGN: right">N</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</td> <td>Paper</td> <td style="TEXT-ALIGN: right">P

</td></tr></tbody></table>
to show

<table border="1" cellpadding="0" cellspacing="0"><tbody><tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><td>
</td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</td> <td style="BACKGROUND-COLOR: #99cc00">Items</td> <td style="BACKGROUND-COLOR: #99cc00">Effect</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</td> <td>Apple</td> <td style="TEXT-ALIGN: right">-</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</td> <td>Orange</td> <td style="TEXT-ALIGN: right">-</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</td> <td>Mouse</td> <td style="TEXT-ALIGN: right">1</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td> <td>Book</td> <td style="TEXT-ALIGN: right"></td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td> <td>Watch</td> <td style="TEXT-ALIGN: right">1
</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</td> <td>Paper</td> <td style="TEXT-ALIGN: right">-</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">
</td> <td>
</td> <td style="TEXT-ALIGN: right">
</td></tr> <tr style="HEIGHT: 18px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">
</td> <td>
</td> <td style="TEXT-ALIGN: right">
</td></tr></tbody></table>

As apple/paper has Negative and Positive meaning nil at the end.

 

[Andrew Poulsom]

The code is designed to work with the data in your first post.