Pivot Chart

Thursday, May 29th, 2014

This is the Pivot Chart our participants for the Hidden Secrets in Data Analysis with Excel course learned to create today.

Stats on Gender

Pivot chart that makes a difference to your presentation

Work Order Status

Monday, May 16th, 2011

Background

1. I will receive work order from planning section
2. I will key those information into excel “work order” sheet from column C to G
3. My production member will follow the work order to assemble as per required
4. After completed, I request them to key in the instrument serial number + details on “Instrument SN” sheet.
5. I create “pivot table” so to understanding the daily output of requirement
6. I want to link the daily output qty from “Pivot table” sheet to “Work order” sheet so to monitor whether Work order completed.

Problem encountered:

Step 6: I need to match date from “Pivot table” to date to “work order” as well as work order no. from “pivot table” to “work order”. I have no idea how to do for 2 dimensional matching. At first I try to use sumif but fail.

Answer

I used GetPivotData, a formula that can be used to extract data from Pivot Table dynamically for this. You can change the parameters to pick up the data basd on 2 conditions, the workorder and the date. The number picked up is already a consolidated number from pivot table. I have combined it with IF and ISERR to make sure that all the cells return zero if no data is found.
 
You can find the solution in this file.

Which view is good for Pivot Table and data management?

Tuesday, March 1st, 2011

span style=”font-family: Arial, Helvetica, sans-serif;”strongQuestion:/strong I have a large data of about 700 columns amp; 4000 rows relating to the sales.Great of columns(fields) are due to no. of sale-able items (About 670)/spanbr /span style=”font-family: Arial, Helvetica, sans-serif;”/spanbr /br /span style=”font-family: Arial, Helvetica, sans-serif;”I have designed the table with each individual sale-able items as separate Fields instead of One field under Item Name as I want to view sales transaction in one record. This is to overcome disadvantage of entering data for party name, bill no, date…etc several times for sales transactions having more than one items./spanbr /span style=”font-family: Arial, Helvetica, sans-serif;”But when I converted the data into Table amp; make use of Database functions like dsum, dget to extract the results, it slows down.Calculation is very slow,even if I open or save the excel file it takes a lot of time./spanbr /br /span style=”font-family: Arial, Helvetica, sans-serif;”I tried pivot table but it didn’t worked As I need to drag and drop almost 650 fields./spanbr /span style=”font-family: Arial, Helvetica, sans-serif;”br //spanbr /strongspan style=”font-family: Arial, Helvetica, sans-serif;”Answer:/span/strong br /span style=”font-family: Arial, Helvetica, sans-serif;”The correct approach is what you have been avoiding, that is, to put the items names under One column. This arrangementnbsp;facilitate the use of Pivot Table to generate results. What you are concerned with, to view transactions in one records cannbsp;still be achievednbsp;in the Pivot Table view, instead of the raw data view. Right not, you have no choice but to re-organise everything in the other view. A macro can be very helpful for this situation./span

s2Member®