Pivot Tables filtering data

Using Excel, I have query(using an ODBC connection) of all customer order 
detail over a two year time period.  The detail for each customer is 
different in that some customer have multiple orders, and the amount of 
individual sales parts they order may be 1 or 100.  My query gathers detail 
by order line item.  We were able to provide management with comparative 
sales data.

Now that we have shown this report to management, they want three things 
that I'm unsure how to provide.  1) Customers that are new in the current 
year2) customers that had orders in both years, 3)customers that ordered in 
the prior year that have no cy orders.

Using the pivot table funtion, is there any way to filter data, so that if 
both years are >0, then show those customers? To show if the difference 
between the data is positive or negative, only those customers, respectively? 
 

Remember that the line item detail in my query only shows one sales part, 
for one year's activity per row.

Thank you.


0
ExcelGuRu (23)
1/7/2005 5:23:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
561 Views

Similar Articles

[PageSpeed] 46

I've ran into this type of problem before. 

The best I've managed to accomplish is to display
- the years in down the left columns
- the customer # along the top row 
- and a count of the records in the data area
By manually viewing this  "matrix" of information you can visially see
the gaps and years that have orders. By using the drill down on the
subtotals you can inspect the records in greater detail. Granted, this
method wouldn't work for a large setup of unique customers but should
get you through the night on smaller data sets. 

I'd be interested if someone else has something else to offer. MS needs
to add more functionality to PTs


-- 
cs02000
0
11/24/2005 8:41:59 PM
Reply:

Similar Artilces:

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

How to change pivot table source database?
How can I change the source database for a pivot table in an Excel spreadsheet. Our database server has been changed and when I create a new dsn pointing to the correct server, I get an error message indicating that the query on the old server is still trying to be used. Thanks, Steve This should work for you (?) Open the pivot table and click on it. Menu - Data/Pivot Table report. Click the Back button on the Wizard dialog that appears and connect t the new data source -- Message posted from http://www.ExcelForum.com ...

refresh pivot tables
if i have my pivot table not set to refresh on open. is there a way to refresh all of them at once. when i want them to be refreshed? does F9 do this? from the help, F9 says this: Calculates all worksheets in all open workbooks. F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+...

Mapping Geographic Data
I want to create a data map and I need to have Microsoft Map installed. Apparently Microsoft Map was not installed when our IT Department installed Excel (I am running Excel 2003). I believe I ahve to rerun Setup to install Microsoft Map however my IT Department says they can't find Microsoft Map in the Setup file. Can any one help me tell them how to locate Microsoft Map in the Setup file? Hi, Map was dropped as a free add on to excel after xl2000, I think that's the version. Anyhow to get Maps in xl2003 you will need to buy the standard alone program. Cheers Andy Mike wro...

Diagonal Pivot Table?
Hi, I currently have data that looks something like A | B | C 1 | 1 | 5 1 | 2 | 9 2 | 1 | 7 2 | 2 | 4 (but obviosuly many times bigger) If I put this in a pivot table, with A and B on the axis, it would loo something like: _ | 1 | 2 1 | 5 | 9 2 | 7 | 4 This is because it counts (1,2) as different from (2,1). Is there an way to concatonate the two? i.e.: _ | 1 | 2 1 | 5 | 2 | 8 | 4 Let me knwo if this doesn't make sense. Thanks -- Message posted from http://www.ExcelForum.com Wait, cancel that! Turns out I don't need to worry. thanks anyway, - -- Message posted from http://...

how to jump from form to related record in table
I am trying to deal with the limits on updating fields in queries. So, in a form that shows data re orders waiting to be shipped, I'd like to be able to doubleclick on the order_ID number field (let's say order # 02975) and have that cause a related form or table to open at record number 02975. Then, I can directly edit the data that needs editing. Using Access 2000 "Macro builder" and the On Dbl Click event, I have been able to get a desired form to open and have the focus, but I cannot get the related record to be selected. (I'd prefer selected to filtered.) I am ho...

Linked cells, make the data go hard manually
I'd like to convert a cell linked to a previous day to hard numbers once a day. Is that possible? By 'hard number' you mean one that is no longer linked, you could copy, then paste as, paste value. HTH, Carole O "Johnny" wrote: > I'd like to convert a cell linked to a previous day to hard numbers once a > day. Is that possible? ...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

How to reach through subform to underlying table?
With VBA I've learned to pull data from fields on my subform like this: sbfVendorDetail.Form.[Start Date]. However, fields such as the ID may not be on the subform since the user doesn't need to see it and it would take up space. Is there a way to read the ID field of the current record of the subform without putting the field on the form? "WDSnews" <wdsnews.0640@oregoncity.com> wrote in message news:eQAVmQ$5KHA.980@TK2MSFTNGP04.phx.gbl... > With VBA I've learned to pull data from fields on my subform like this: > sbfVendorDetail.Form.[Sta...

Printing Autofilter Data
How do I print Autofilter data onto one sheet instead of a few lines on say 20 pages? In page Setup, select fit to print and select 1 page wide and 1 page high. -- Regards, Tom Ogilvy "Confused" <Confused@discussions.microsoft.com> wrote in message news:2C7DCB67-06B2-429C-99D9-D2B3A40FEF4A@microsoft.com... > How do I print Autofilter data onto one sheet instead of a few lines on say > 20 pages? Thank you! "Tom Ogilvy" wrote: > In page Setup, select fit to print and select 1 page wide and 1 page high. > > -- > Regards, > Tom Ogilvy >...

Lookup table oddity
Hi I have a lookup table that was in an assessment exercise and it refuses to work on one cell. There are two lookup tables in the exercise... one looks up the product code - this one works okay. The one that won't work properly is the second one, which uses the supplier as the lookup value. I tested it out on a computer with 2003 at the training centre where I work and it worked. However, it won't work on my 2007, or any of the computers using 2007 that I tried it on. I can attach the file for anyone who is interested in looking at this oddity. It is driving me distracted...

Automatically pull data from one file to populate a template
Hi Everyone... Just beginning to get the hang of vba coding in excel. I would like to implement a new feature in a spreadsheet here at the office. We currently have a master records sheet containing vital information on unit processing. Each unit then has a sub file containing additional information. Currently one must populate the master record file. Then open a template and populate another file with the same information. I would like to streamline this process and have the data in the master record file automatically inserted into the template, allowing the user to only have to ...

SQL Server (XML Data) Through ODBC to MS Access
Hello, In one database in my company. Previously the data in SQL Server, the tables were simple text and number formats and it was easy for me to link the data through ODBC to MS Access and I could do all sorts of queries and calculations, etc. But, now (after several months) I realize that the DBA and Programmer changed, the data in SQL Server to be XML Data Type in the new setup. In SQL Server. dbo.FlightDocuments Table docID, int docInfo, xml docLogs, xml and so on. and I see the data in Access (through ODBC) for one of the fields of the above table like this: ...

Save data in a worksheet in fixed length fields...
I have data in a worksheet (Office 2000) which I would like to export in fixed length format for importing into another application. Is there a way to use column settings or a wizard to export the file in correct format rather than exporting and then having to do signficant editing before I can import the file into the other application? one way: http://www.mcgimpsey.com/excel/textfiles.html#fixedfield In article <9C97DB7F-65EB-4438-926E-23906D56E75D@microsoft.com>, dietzd <dietzd@discussions.microsoft.com> wrote: > I have data in a worksheet (Office 2000) which ...

I want to enter a customer # and retreive their data
How do I retreive data, which I've entered, from another worksheet when I enter a code or # that is specific to that data? Assuming you have a yable and the data is set up like code1 value value etc code2 value value etc and so on then you can use =VLOOKUP(C1,Sheet2!A2:C50,2,0) where C1 is the typed in lookup value (code) and A2:C50 the table and this particular formula will return the value from column 2 lookup VLOOKUP in help -- Regards, Peo Sjoblom Nothwest Excel Solutions www.nwexcelsolutions.com remove ^^ from email "It is a good thing to fol...

Aggregating Data in Fifteen Unopened Spreadsheets into Single Consolidated Sheet
I have a series of 15 spreadsheets, all having the same column structure. They all have the same filename barring first two letters which refer to a persons initials. The 15 files are stored in the same directory. In that directory I'd like to create a sixteenth file which consolidates much of the data in the 15 files. I want to use that consolidated data in a pivot table. I know that the top-left- hand-corner of the range I want within each of the 15 spreadsheet files is the Cell A10. I cannot be so precise with the bottom right hand corner (BRHC) as the number of rows is variable i...

How to organize data?
I am a frustrated Excel newbie. I grit my teeth every time I have to use Excel. But I need to create a small database of records and chart it's monthly progression, and I think Excel is probably the best program for the job. Only, I don't know who to do this. Please bear with me as I really need help with this. Say that I have a short list of vendors in different cities. For each vendor I have a total # of items in their inventory and another # of the # of items sold from the inventory, and a percentage of that. So I have the vendors listed in Column A. # of items in inventory ...

Data Normalization
Greetings, I am not sure which newsgroup to ask this question. Any directions would be most appreciated. I have a list of about 5000 customers over a 30 year period. I am trying to give each customer a unique ID. Some of these customers have moved a few times, some of them have gotten married a few times (we deal with the wives primarily), some have multiple service addresses. This question has plagued me for the last 30 years!!!! Any suggestions would be most welcome. TIA -Minitman Hi probably better asked in a database related newsgroup. What you're asking for is quite typic...

Pivot Chart???
I'm trying to create a chart showing the number of orders per week fo each of our customers. Each row (excluding header) in my Data Worksheet is an order wit Client Name and Week# that the order was placed in. I have a pivot table with "Client Name" in the page field and Week and total (count) displaying correctly. I need the X axis to remai static for all 52 weeks (with labels) and not adjust and show just th weeks with data. I tried creating 2 more columns to chart from. Week (a list wit values of 1 - 52) and Total. I'm trying to pull data from the pivo table to fill...

Using CTreeCtrl to display streaming data
Hi there, I'm going to be creating an application that displays test data to a certain processor. The default view is to show the overall status of the processor. If there is an error shown, the user can click that processor and specific devices within the processor will be shown with their status. Now here is what I want to do with CTreeCtrl, since I'm brand new to MFC I just need to know what I want is feasible... to get simpler test data, a certain message has to be continously pinged to the processor. To get the detailed test data, more messages have to be sent. Is t...

Exclude a column from data import
Hi there, I am using Excel 2003 and am importing live data into a sheet. Everytime the page loads the current data is pulled in from an SQL 2000 view. The person who wants to use this spreadsheet wants to put an extra column in the middle of the data. I have created a null column in the view so that it is blank, but when you insert something into the column and and refresh occurs, the column quite rightly gets overwritten with null. Is there a way to remove the data from just one column? I understand (I think) that if the order changes, or if a row is deleted from the table, the manual...

Find the number of rows returned in a filter
I would like to filter a list and test for no match. For example Sub FilterTest(market as string) Excel.Application.Screenupdating = false Excel.sheets("SS").visible = true Excel.sheets("SS").select Excel.sheets("SS").range("B10").select Excel.Selection.AutoFilter Excel.Selection.AutoFilter Field:=1, Criteria1:="<>1*", Operator:=xlAnd, _ Criteria2:="<>*T" Excel.Application.screenupdating = true 'now here is what I want to test If (the filter retuns no visible records or rows) then Msgbox "The "&...

Pivot Table
Hello. I have a pivot table that tracks my medical expenses and insurance benefits. The layout uses the "Provider" field as a PAGE filter, and so I can see which provider is being displayed when I choose their name from the list. However, there are times that I want to view the information for ALL providers. But when I choose the "ALL" selection from the list, I can no longer tell which information goes with which provider because the provider's name is not one of the fields listed in the "ROW" layout. I have tried to add the Provider field to both the P...

[Q] Converting table
Hi Can someone tell me how to convert a table? The table was originally set up for the original POS data The new format of the POS data doesnt have the DeptNum but instead the DeptDesc In any event the table(DeptSalesDetail) that currently contains a field for Deptnum table(Depts) contains Fields (DeptID, Deptnum, DeptDesc, CatID...) What I would like to do is to add the field DeptID and eliminate the DeptNum from the table(DeptSalesDetail) How do I get the correct DeptID from the table(depts) to get in to the DeptID in the table( DeptSalesDetail) This way I can have a lookup from the ...

Pivot Table Formatting #7
Some of the formatting on my Pivot Tables remain after refreshing, some formatting does not. I have "preserve formatting" checked, "Autoformat Table" unchecked. Excel 2002. Any help would be appreciated - I have had formatting problems with Pivot Tables for years!! You may have to enable selection (From the Pivot toolbar, choose PivotTable>Select, and click on Enable Selection) Before formatting cells, use the selection feature to select the cells. For example, move the pointer to the top of a column in the pivot table (just above the column's heading cell...