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>

 

[Andrew Poulsom]

Here's a pivot table:

<TABLE style="WIDTH: 90pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=120 x:str><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17 width=85>Sum of Effect</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23 width=35> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>Items</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl23>Total</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl22 height=17>Apple</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl25 x:num="0"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>Book</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl26 x:num="-1"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>Mouse</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl26 align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>Orange</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl26 x:num="-1"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl24 height=17>Paper</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl26 x:num="0"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl27 height=17>Watch</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class=xl28 align=right x:num>1</TD></TR></TBODY></TABLE>

I formatted the Total cells as #;"";"".

 

[mirrorwatch]

Here's a pivot table:

<table style="WIDTH: 90pt; BORDER-COLLAPSE: collapse" x:str="" border="0" cellpadding="0" cellspacing="0" width="120"><colgroup><col style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width="85"><col style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width="35"></colgroup><tbody><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class="xl22" height="17" width="85">Sum of Effect</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class="xl23" width="35">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class="xl22" height="17">Items</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class="xl23">Total</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: #f0f0f0" class="xl22" height="17">Apple</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class="xl25" x:num="0">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class="xl24" height="17">Book</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class="xl26" x:num="-1">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class="xl24" height="17">Mouse</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class="xl26" x:num="" align="right">1</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class="xl24" height="17">Orange</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class="xl26" x:num="-1">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class="xl24" height="17">Paper</td><td style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class="xl26" x:num="0">
</td></tr><tr style="HEIGHT: 12.75pt" height="17"><td style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class="xl27" height="17">Watch</td><td style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 0.5pt solid" class="xl28" x:num="" align="right">1</td></tr></tbody></table>

I formatted the Total cells as #;"";"".

That is correct, but not want I need.

There is tons of items so I want some vba code/macro.... or basically a faster way to have a table similar to yours

 

[Andrew Poulsom]

It doesn't matter how many items there are. I created that pivot table in about 20 seconds.

 

[mirrorwatch]

It doesn't matter how many items there are. I created that pivot table in about 20 seconds.

Actually I wanted a button to trigger in making the table. So prefer, vba

On the other hand, how exactly to do that you did without editing anything to the original table.( thereby having 2 tables, one table as my top post and one table like yours)

 

[Andrew Poulsom]

Here's a pivot table tutorial:

http://www.contextures.com/CreatePivotTable.html

You can use the macro recorder to get some sample code.

 

[mirrorwatch]

Look at it, but was unable to find

how exactly to format the Total cells as #;"";"".like you did.

And if it is another scenrio where I write a letter like P for 1 and N for -1, how to get the table then?

 

[Andrew Poulsom]

Select the cells, right click, choose Format cells, select Custom, enter the format in the Type box and click OK.

For your second question use the number format "P";"N";"".

 

[mirrorwatch]

Last question

So how can I do it faster instead of formatting every cell each time in the total session?

And the second question was like this
<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>

 

[Andrew Poulsom]

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