Calculated field in Pivot Table

N

niam07

New Member
Joined
Mar 13, 2014
Messages
11
Hi ya,

I have been searching all through the forum but cannot get my head around Pivot Table calculated field, will be very appreciate of any help. I have raw data as following. I buy stock from different suppliers. Every item has a code. If I buy same stock from different suppliers, my code remains same. My category determines whether I am buying or selling stock.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Suppliers or client[/TD]
[TD]Code
[/TD]
[TD]Category[/TD]
[TD]Item[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Supplier 1[/TD]
[TD]1001[/TD]
[TD]Purchase[/TD]
[TD]Sofa[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Supplier 1[/TD]
[TD]1002[/TD]
[TD]Purchase[/TD]
[TD]Bed[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]1001[/TD]
[TD]Sell[/TD]
[TD]Sofa[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Supplier 2[/TD]
[TD]1001[/TD]
[TD]Purchase[/TD]
[TD]Sofa[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]1001[/TD]
[TD]Sell[/TD]
[TD]Sofa[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]1002[/TD]
[TD]Sell[/TD]
[TD]Bed[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

I need Pivot table summary as following where it gives me the balance of my remaining stock.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Code
[/TD]
[TD]Sum of Purchase Quantity[/TD]
[TD]Stock Remaining[/TD]
[/TR]
[TR]
[TD]Sofa[/TD]
[TD]1001[/TD]
[TD]20[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Bed[/TD]
[TD]1002[/TD]
[TD]30[/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]

Thanks a lot.

Naim
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
You can try with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Suppliers or client[/td][td=bgcolor:#5B9BD5]Code[/td][td=bgcolor:#5B9BD5]Category[/td][td=bgcolor:#5B9BD5]Item[/td][td=bgcolor:#5B9BD5]Quantity[/td][td][/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Code[/td][td=bgcolor:#70AD47]Purchase[/td][td=bgcolor:#70AD47]OnStock[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Supplier 1[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Sofa[/td][td=bgcolor:#DDEBF7]
15​
[/td][td][/td][td=bgcolor:#E2EFDA]Sofa[/td][td=bgcolor:#E2EFDA]
1001​
[/td][td=bgcolor:#E2EFDA]
20​
[/td][td=bgcolor:#E2EFDA]
16​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Supplier 1[/td][td]
1002​
[/td][td]Purchase[/td][td]Bed[/td][td]
30​
[/td][td][/td][td]Bed[/td][td]
1002​
[/td][td]
30​
[/td][td]
29​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Client 1[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Sell[/td][td=bgcolor:#DDEBF7]Sofa[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Supplier 2[/td][td]
1001​
[/td][td]Purchase[/td][td]Sofa[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Client 2[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Sell[/td][td=bgcolor:#DDEBF7]Sofa[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Client 3[/td][td]
1002​
[/td][td]Sell[/td][td]Bed[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Quantity"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Code"}, {{"Count", each _, type table}, {"Purchase", each List.Sum([Purchase]), type number}, {"Sell", each List.Sum([Sell]), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "OnStock", each [Purchase]-[Sell]),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Item", each List.Distinct(Table.Column([Count],"Item"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Item", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Reordered Columns" = Table.ReorderColumns(#"Extracted Values",{"Item", "Code", "Count", "Purchase", "Sell"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Count", "Sell"})
in
    #"Removed Columns"[/SIZE]
 
Upvote 0
Hi Sandy,

Thanks a lot. Sorry for being dummy but where do I enter the code that you have provided. I am using office 365 and Googled but cannot find where to input this code. if you could provide me bit detailed info on how to input this code please.

Thanks,
Niam
 
Upvote 0
Hi Sandy,

Thanks a lot. Sorry for being dummy but where do I enter the code that you have provided. I am using office 365 and Googled but cannot find where to input this code. if you could provide me bit detailed info on how to input this code please.

Thanks,
Niam
 
Upvote 0
- Create Table from your source table (Ctrl+T) Make sure the name of this table is Table1 (Formulas - Name Manager)
- Click inside the table
- Data - From table

you will see PowerQuery editor

go to Home - Advanced Editor

- delete all what you see there then paste code from the post
- Close & Load - you should see new sheet with result table
-
here is example file
 
Last edited:
Upvote 0
Another option, which AFAIK works for any Excel version with pivot tables. So since the mid 90s?

Give the source data a simple (non-dynamic) defined name. say YourTableName then save the file. From a new file (CTRL-N), start the pivot table wizard (ALT-D-P) and choose external data source, next, get data, Excel files, OK, browse for the file, OK, see the defined name, select a field or more, then follow wizard to the end choosing the option to edit in MS Query at the last step. Via the SQL button change the text to
Code: 
SELECT Item, Code, IIF(Category='Purchase',1,0)*Quantity AS [Purchase Quantity], IIF(Category='Purchase',1,-1)*Quantity AS [Stock Remaining]
FROM YourTableName
OK to enter, OK to acknowledge message about not being able to graphically represent, then via the open door icon exit MS Query & make the pivot table. Once created, the worksheet containing the pivot table can be moved into the source workbook if you like.

If you want other fields in the pivot table, then add them in the above definition.

If you want the SUM directly in the dataset without the details, then use
Code: 
SELECT Item, Code, SUM(IIF(Category='Purchase',1,0)*Quantity) AS [Purchase Quantity], SUM(IIF(Category='Purchase',1,-1)*Quantity) AS [Stock Remaining]
FROM YourTableName
GROUP BY Item, Code

And, instead of a pivot table this could be just a query.
 
Last edited:
Upvote 0
Hi Sandy,

Thanks for this. I found it easy to download your file and make changes there. It worked for some values but for some reason as my raw data increased, i.e. as my codes increased in my table after 1006, the quantity part is not showing.

I have posted my raw data and desired query as follows. Will really appreciate your input into this. For some reason, output does not does not add up all quantities. Thanks a lot.

Raw Data

[TABLE="width: 493"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Item[/TD]
[TD]Code[/TD]
[TD]Suppliers or client[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 1[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Tea Table[/TD]
[TD]1003[/TD]
[TD]Supplier 2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Mattress[/TD]
[TD]1004[/TD]
[TD]Supplier 3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Mattress[/TD]
[TD]1004[/TD]
[TD]Supplier 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Bed[/TD]
[TD]1002[/TD]
[TD]Supplier 2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Bed[/TD]
[TD]1002[/TD]
[TD]Supplier 2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Bed[/TD]
[TD]1002[/TD]
[TD]Supplier 2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Dinning Table[/TD]
[TD]1005[/TD]
[TD]Supplier 5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Dinning Table[/TD]
[TD]1005[/TD]
[TD]Supplier 5[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Divan[/TD]
[TD]1006[/TD]
[TD]Supplier 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Sofas[/TD]
[TD]1001[/TD]
[TD]Supplier 6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Purchase[/TD]
[TD]Office Chair[/TD]
[TD]1007[/TD]
[TD]Supplier 6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Office Chair[/TD]
[TD]1007[/TD]
[TD]Supplier 6[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 493"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]
Required Output

[TABLE="width: 308"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Code[/TD]
[TD]Purchase[/TD]
[TD]Sell[/TD]
[TD]OnStock[/TD]
[/TR]
[TR]
[TD]Bed[/TD]
[TD="align: right"]1002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dinning Table[/TD]
[TD="align: right"]1005[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Divan[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mattress[/TD]
[TD="align: right"]1004[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office Chair[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sofas[/TD]
[TD="align: right"]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tea Table[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]





[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
With your new example:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Category[/td][td=bgcolor:#5B9BD5]Item[/td][td=bgcolor:#5B9BD5]Code[/td][td=bgcolor:#5B9BD5]Suppliers or client[/td][td=bgcolor:#5B9BD5]Quantity[/td][td][/td][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Code[/td][td=bgcolor:#70AD47]Purchase[/td][td=bgcolor:#70AD47]OnStock[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Sofas[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Supplier 1[/td][td=bgcolor:#DDEBF7]
60​
[/td][td][/td][td=bgcolor:#E2EFDA]Bed[/td][td=bgcolor:#E2EFDA]
1002​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Tea Table[/td][td]
1003​
[/td][td]Supplier 2[/td][td]
3​
[/td][td][/td][td]Dinning Table[/td][td]
1005​
[/td][td]
5​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sales[/td][td=bgcolor:#DDEBF7]Sofas[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Supplier 1[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]Divan[/td][td=bgcolor:#E2EFDA]
1006​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Mattress[/td][td]
1004​
[/td][td]Supplier 3[/td][td]
2​
[/td][td][/td][td]Mattress[/td][td]
1004​
[/td][td]
4​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Mattress[/td][td=bgcolor:#DDEBF7]
1004​
[/td][td=bgcolor:#DDEBF7]Supplier 4[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]Office Chair[/td][td=bgcolor:#E2EFDA]
1007​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Bed[/td][td]
1002​
[/td][td]Supplier 2[/td][td]
1​
[/td][td][/td][td]Sofas[/td][td]
1001​
[/td][td]
69​
[/td][td]
66​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Bed[/td][td=bgcolor:#DDEBF7]
1002​
[/td][td=bgcolor:#DDEBF7]Supplier 2[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td=bgcolor:#E2EFDA]Tea Table[/td][td=bgcolor:#E2EFDA]
1003​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sales[/td][td]Bed[/td][td]
1002​
[/td][td]Supplier 2[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Sofas[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Supplier 1[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sales[/td][td]Sofas[/td][td]
1001​
[/td][td]Supplier 1[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Sofas[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Supplier 1[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Dinning Table[/td][td]
1005​
[/td][td]Supplier 5[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sales[/td][td=bgcolor:#DDEBF7]Dinning Table[/td][td=bgcolor:#DDEBF7]
1005​
[/td][td=bgcolor:#DDEBF7]Supplier 5[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Divan[/td][td]
1006​
[/td][td]Supplier 4[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Purchase[/td][td=bgcolor:#DDEBF7]Sofas[/td][td=bgcolor:#DDEBF7]
1001​
[/td][td=bgcolor:#DDEBF7]Supplier 6[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Purchase[/td][td]Office Chair[/td][td]
1007​
[/td][td]Supplier 6[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Sales[/td][td=bgcolor:#DDEBF7]Office Chair[/td][td=bgcolor:#DDEBF7]
1007​
[/td][td=bgcolor:#DDEBF7]Supplier 6[/td][td=bgcolor:#DDEBF7]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Category]), "Category", "Quantity", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Suppliers or client"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Code"}, {{"Count", each _, type table}, {"Purchase", each List.Sum([Purchase]), type number}, {"Sales", each List.Sum([Sales]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "OnStock", each [Purchase]-[Sales]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Sales"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Item", each List.Distinct(Table.Column([Count],"Item"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Item", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns2" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns2",{"Item", "Code", "Purchase", "OnStock"})
in
    #"Reordered Columns"[/SIZE]

I think you can try to learn step by step how it works
Microsoft Power Query for Excel Help

and here is example again: example file
 
Upvote 0
Brilliant, it works. Thanks a lot Sandy. Highly appreciated your help. Just on the output, I also wanted selling column. And the on stock, I wanted it to show "purchase - sell" , and if there is no sell, just the purchase amount. Thanks again.
 
Last edited:
Upvote 0
in PQ editor - Applied steps - delete step: Remove Column1 then reorder columns as you wish and Close&Load


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Item[/td][td=bgcolor:#70AD47]Code[/td][td=bgcolor:#70AD47]Purchase[/td][td=bgcolor:#70AD47]Sales[/td][td=bgcolor:#70AD47]OnStock[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Bed[/td][td=bgcolor:#E2EFDA]
1002​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dinning Table[/td][td]
1005​
[/td][td]
5​
[/td][td]
2​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Divan[/td][td=bgcolor:#E2EFDA]
1006​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mattress[/td][td]
1004​
[/td][td]
4​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Office Chair[/td][td=bgcolor:#E2EFDA]
1007​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sofas[/td][td]
1001​
[/td][td]
69​
[/td][td]
3​
[/td][td]
66​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Tea Table[/td][td=bgcolor:#E2EFDA]
1003​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]

check example file from post#8
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
to to calculated the weighted average if the inventory above zero
Replies
1
Views
355
dermie_72
D
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
958
Alex Blakenburg
A
W
  • Question Question
VBA InputBox without entry quitting Sub/Macro
Replies
1
Views
549
Micron
M
J
  • Question Question
pivot table pull value if name changes
Replies
1
Views
189
Kaper
Kaper
C
  • Question Question
Confused over pivot table calculations :-(
Replies
1
Views
638
number kruncher
N

Forum statistics

Threads
1,224,747
Messages
6,180,714
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top