Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Compare % Difference 2 Columns In A Pt

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi
I have an Excel 2010 Pivot table .
I want to compare the % difference between customer sales in 2009 and 2008.
I have 3 columns at present Customer name ,Sales 2009 and Sales 2008. I would like a fourth column to show the % difference .Can someone assist me by outlining the process to do this. Much appreciated


Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Reverse Row or Column Order in a Worksheet
- This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics







Hi all

I have a pivot table with two columns; Sales 2008 & Sales 2009.

It is split by Customer.

When I generate the pivot, I can look at the 2008 Sales vs. the 2009 Sales by Customer but I cannot insert a variance column into the pivot. (i.e the percentage difference between both years).

If I insert the variance at source level then it merely adds the percentages together for my customer subtotals.

I basically want to have a variance column that calculates the variance of what ever data I have highlighted.

Can anyone help?

Da-shee


I'd like to compare Unit Sales from 2009 against Unit Sales from 2008. My source data give me Sales Date and I can grab Unit Sales count in the Value Field of the Pivot Table. I've already got other items as Column and Row Labels so I can't simply list the Sales Dates there. Additionally I need to add calculated fields based on the 2009 vs. 2008 Unit Sales. Effectively I want the end result to looks like this:

Column Label
Row Label------Model 1-----------Delta-----------% Change-------Model 2-----------Delta-------------% Change
Make 1------2009 | 2008------('09 minus '08)------('09 / '08)------2009 | 2008------('09 minus '08)------('09 / '08)
Make 2------2009 | 2008------('09 minus '08)------('09 / '08)------2009 | 2008------('09 minus '08)------('09 / '08)
Make 3------2009 | 2008------('09 minus '08)------('09 / '08)------2009 | 2008------('09 minus '08)------('09 / '08)
Make 4------2009 | 2008------('09 minus '08)------('09 / '08)------2009 | 2008------('09 minus '08)------('09 / '08)

Model 2, Model 3, etc. repeat the Delta and % Change calculated fields of Model 1.

How do I go about doing this? Do I filter the 'sub' columns somehow? How do I add the calculated fields?

Thanks!


Hi
i am looking for a macro to generate all possible combinations of N elements in C columns

for example I have 3 columns
CoL 1 Account
Col 2 Month
Col 3 Year

Independant of number of elements in each column I want to get all possible combinations of 3 elements ( not 2 or 1 element only )

example
Base is

ACCOUNT MONTH YEAR Sales jan 2007 Expenses feb 2008 Material mar 2009
apr

may

jun

jul

aug
number of combination is 3 x 8 X 3 in that case 72 lines to generate but keeping data in columns ( not concatenate field)

result is 72 lines in 3 columns

ACCOUNT MONTH YEAR Sales jan 2007 Sales feb 2007 Sales mar 2007 Sales apr 2007 Sales may 2007 Sales jun 2007 Sales jul 2007 Sales aug 2007 Expenses jan 2007 Expenses feb 2007 Expenses mar 2007 Expenses apr 2007 Expenses may 2007 Expenses jun 2007 Expenses jul 2007 Expenses aug 2007 Material jan 2007 Material feb 2007 Material mar 2007 Material apr 2007 Material may 2007 Material jun 2007 Material jul 2007 Material aug 2007 Sales jan 2008 Sales feb 2008 Sales mar 2008 Sales apr 2008 Sales may 2008 Sales jun 2008 Sales jul 2008 Sales aug 2008 Expenses jan 2008 Expenses feb 2008 Expenses mar 2008 Expenses apr 2008 Expenses may 2008 Expenses jun 2008 Expenses jul 2008 Expenses aug 2008 Material jan 2008 Material feb 2008 Material mar 2008 Material apr 2008 Material may 2008 Material jun 2008 Material jul 2008 Material aug 2008 Sales jan 2009 Sales feb 2009 Sales mar 2009 Sales apr 2009 Sales may 2009 Sales jun 2009 Sales jul 2009 Sales aug 2009 Expenses jan 2009 Expenses feb 2009 Expenses mar 2009 Expenses apr 2009 Expenses may 2009 Expenses jun 2009 Expenses jul 2009 Expenses aug 2009 Material jan 2009 Material feb 2009 Material mar 2009 Material apr 2009 Material may 2009 Material jun 2009 Material jul 2009 Material aug 2009

Thanks in Advance for helping


Can anyone help with this one?
I use Excel 2003 and have a spreadsheet that gives all sales to all customers over a period of time. Currently for 2009 it's at 46,000 rows so I have to select out the sales that are exactly the same but debit and credit as give a total between the two entries of zero. An example of this would be:

Year Month Customer Length Volume Value
2009 May A Customer & Co Ltd 3.0 (0.79) (118.50)
2009 May A Customer & Co Ltd 3.0 (1.02) (152.25)
2009 May A Customer & Co Ltd 3.0 (2.14) (321.45)
2009 May A Customer & Co Ltd 3.0 (1.33) (198.75)
2009 May A Customer & Co Ltd 3.0 1.02 152.25
2009 May A Customer & Co Ltd 3.0 2.14 321.45
2009 May A Customer & Co Ltd 3.0 0.66 99.45
2009 May A Customer & Co Ltd 3.0 1.03 154.35
2009 May A Customer & Co Ltd 3.0 0.79 118.50
2009 May A Customer & Co Ltd 3.0 (0.66) (99.45)
2009 May A Customer & Co Ltd 3.0 1.03 154.35
2009 May A Customer & Co Ltd 3.0 1.33 198.75

Does anyone have an "easy" method of doing this rather than manually?

Thanks again.

Cheers

Aargh!!


I have a pivot table which shows sales by customer by year. I created a field to show the difference in sales year over year. Is there a way to only show the comparison field once? It currently shows up in both 2009 and 2010. Also, I want to then sort the table by the difference in sales. This all seems basic to me but I can't get it to work. Thanks.


I've got a 12-month rolling average sales report that needs to be updated monthly. I've been doing the inserting of data by hand and need an Excel formula to speed up the job.

REPORT FORMAT
Column A contains names of all customers who purchased in 2008 and any who purchased only in 2009, in no special order.

Column B contains the 2008 sales volume for each customer, aligned with that customer's name in Column A. Volume for 2009-only customers is recorded as 0.

Column C contains the sales volume of each customer who purchased Jan-Mar 2008, aligned with that customer's name in Column A.

Column D contains the sales volume of each customer who purchased Jan-Mar 2009, also aligned with that customer's name in Column A.

Columns E & F contain the names and sales volume for all customers who purchased Jan-APRIL 2008. Customer names are not in the same order as in Column A.

Columns G & H contain the names and sales volume for all customers who purchased Jan-APRIL 2009. Customer names are not in the same order as in Column A.

Both E&F and G&H may contain customer names not previously listed.

The formula needs to replace the Column C data with that from Column F and the Column D data with that from Column H, aligning it with the customer names as listed in Column A. Ideally, this would not require an alphabetical sorting of the customer names.

Thanks in advance for your help.


Hi everyone,

Im struggling with this and not sure if its even possible..

I want a bar chart that show the customer along the bottom and sales value up the left hand side. I also want it to show year 2009 and 2010 alongside eachother in the actual graph. Problem is that the 2009 sales are in the same column as 2010 sales. Is there a way to do this please?

Customer Year Sales
JC Ltd 2009 100
BC Ltd 2009 200
IF Ltd 2009 300
JC Ltd 2010 544
BC Ltd 2010 220
IF Ltd 2010 112

Thanks,

John


Hello all,
Hoping someone out there can help. Seems basic, but I can't find an answer. Given a simple set of data below, how can you put in another column to show the $ difference from year to year when the columns are NOT next to one another???? If you use the "difference from" functionality, it just doesn't work properly (puts in a blank column in the current year rather than just to the right side, etc.).

Thanks to anyone that can help!
Brian

Column Labels 2010 2009 Row Labels Sales GP% Sales GP% GrandHarbor $ 2,820,401 27.0% $ 2,819,327 26.7% Strand $ 1,838,666 27.1% $ 1,572,318 27.3% 7Mile $ 834,357 26.1% $ 766,213 24.7% Grand Total $ 5,493,424 26.9% $ 5,157,857 26.6%


I am trying to produce a year over year chart of sales for my company. I have produced a pivot table, and I am using the Pivot Table Field Options and selecting the "% difference from" option. When this option is chosen, it calculates the % difference between whatever fields I choose (in my case months of years). However, when comparing 2008 months to 2009 months, the % difference is -100% for months that have not occurred yet. That is a result of 2009's future months not having any data in them. Therefore, there is a -100% between those months in 2009 and those months in 2008. Is it possible to get the "% difference from" option to not operate when one of the cells it is comparing is not populated with data?

Thanks.


I need to answer the question - how many persons were enrolled on or before 10/10/2008 and on or after 3/19/2009.
I have used the following:
=SUMPRODUCT((A2:A78=3/19/2009))
and have also tried:
=SUMPRODUCT((A2:A78=3/19/2009))
The result from the above formulas is -0-; when in fact there are 19 persons enrolled on or before 10/10/2008 and on or after 3/19/2009.

Thank you for your help.

Enrolled Exited 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 07/01/2009 08/28/2008 03/09/2009 08/28/2008 02/23/2009 08/28/2008 02/17/2009 08/28/2008 02/17/2009 08/28/2008 02/10/2009 08/28/2008 02/06/2009 08/28/2008 02/05/2009 08/28/2008 02/05/2009 08/28/2008 09/23/2008 08/28/2008 09/22/2008 08/28/2008 09/22/2008 08/28/2008 09/17/2008 08/28/2008 09/11/2008 08/28/2008 09/11/2008 08/28/2008 09/08/2008 08/28/2008 09/08/2008 08/28/2008 09/08/2008 08/28/2008 09/05/2008 08/28/2008 09/04/2008 08/28/2008 09/04/2008 08/28/2008 09/02/2008 09/02/2008 07/01/2009 09/02/2008 07/01/2009 09/02/2008 07/01/2009 09/02/2008 07/01/2009 10/27/2008 07/01/2009 10/28/2008 07/01/2009 11/03/2008 07/01/2009 11/03/2008 07/01/2009 11/05/2008 11/14/2008 11/05/2008 11/14/2008 11/06/2008 07/01/2009 11/12/2008 01/27/2009 11/18/2008 07/01/2009 11/18/2008 07/01/2009 11/18/2008 07/01/2009 11/19/2008 07/01/2009 11/19/2008 07/01/2009 11/19/2008 07/01/2009 11/19/2008 01/06/2009 11/20/2008 07/01/2009 11/20/2008 07/01/2009 11/20/2008 11/25/2008 11/21/2008 07/01/2009 12/01/2008 07/01/2009 12/01/2008 07/01/2009 12/16/2008 07/01/2009 01/06/2009 07/01/2009 01/12/2009 07/01/2009 01/12/2009 07/01/2009 01/21/2009 07/01/2009 01/21/2009 07/01/2009 01/21/2009 07/01/2009 02/17/2009 07/01/2009 02/17/2009 07/01/2009 02/20/2009 07/01/2009 02/24/2009 07/01/2009 03/02/2009 07/01/2009 03/02/2009 07/01/2009 03/03/2009 07/01/2009 03/03/2009 07/01/2009 03/10/2009 07/01/2009


Hi,

I need help in identifying an account status based on sales $ for 2009 and 2010. What is the best way to do this? I wrote two functions but don't know how to combine into one function.

Any assistance is appreciated.

Thanks
Lynn


Sales 2009 2010

Customer A $1500 $3000 ----- current customer
Customer B $9000 ---- lost customer
Customer C $10,000 ---- new customer


Hi All,

I've raised this in another query but thought it best to start a new one as it is based on SQL language in a Microsoft Query.

The following SQL code comes up from a Microsoft Query that I run in Excel on a Data Connection to an Access Database.

My question is - does anyone know how to get the scode converted to a 'General' format in the following Microsoft Query SQL code?

Code:

SELECT DISTINCT `101 Sales 2010`.scode, `101 Sales 2010`.Brand, `101 Sales 2010`.Market, `101 Sales 2010`.`1`, `101 Sales 2010`.`2`, `101 Sales 2010`.`3`, `101 Sales 2010`.`4`, `101 Sales 2010`.`5`, `101 Sales 2010`.`6`, `101 Sales 2010`.`7`, `101 Sales 2010`.`8`, `101 Sales 2010`.`9`, `101 Sales 2010`.`10`, `101 Sales 2010`.`11`, `101 Sales 2010`.`12`, `101 Sales 2010`.`13`, `101 Sales 2010`.`14`, `101 Sales 2010`.`15`, `101 Sales 2010`.`16`, `101 Sales 2010`.`17`, `101 Sales 2010`.`18`, `101 Sales 2010`.`19`, `101 Sales 2010`.`20`, `101 Sales 2010`.`21`, `101 Sales 2010`.`22`, `101 Sales 2010`.`23`, `101 Sales 2010`.`24`, `101 Sales 2010`.`25`, `101 Sales 2010`.`26`, `101 Sales 2010`.`27`, `101 Sales 2010`.`28`, `101 Sales 2010`.`29`, `101 Sales 2010`.`30`, `101 Sales 2010`.`31`, `101 Sales 2010`.`32`, `101 Sales 2010`.`33`, `101 Sales 2010`.`34`, `101 Sales 2010`.`35`, `101 Sales 2010`.`36`, `101 Sales 2010`.`37`, `101 Sales 2010`.`38`, `101 Sales 2010`.`39`, `101 Sales 2010`.`40`, `101 Sales 2010`.`41`, `101 Sales 2010`.`42`, `101 Sales 2010`.`43`, `101 Sales 2010`.`44`, `101 Sales 2010`.`45`, `101 Sales 2010`.`46`, `101 Sales 2010`.`47`, `101 Sales 2010`.`48`, `101 Sales 2010`.`49`, `101 Sales 2010`.`50`, `101 Sales 2010`.`51`, `101 Sales 2010`.`52`, `101 Sales 2010`.`Wk 1`, `101 Sales 2010`.`Wk 2`, `101 Sales 2010`.`Wk 3`, `101 Sales 2010`.`Wk 4`, `101 Sales 2010`.`Wk 5`, `101 Sales 2010`.`Wk 6`, `101 Sales 2010`.`Wk 7`, `101 Sales 2010`.`Wk 8`, `101 Sales 2010`.`Wk 9`, `101 Sales 2010`.`Wk 10`, `101 Sales 2010`.`Wk 11`, `101 Sales 2010`.`Wk 12`, `101 Sales 2010`.`Wk 13`, `101 Sales 2010`.`Wk 14`, `101 Sales 2010`.`Wk 15`, `101 Sales 2010`.`Wk 16`, `101 Sales 2010`.`Wk 17`, `101 Sales 2010`.`Wk 18`, `101 Sales 2010`.`Wk 19`, `101 Sales 2010`.`Wk 20`, `101 Sales 2010`.`Wk 21`, `101 Sales 2010`.`Wk 22`, `101 Sales 2010`.`Wk 23`, `101 Sales 2010`.`Wk 24`, `101 Sales 2010`.`Wk 25`, `101 Sales 2010`.`Wk 26`, `101 Sales 2010`.`Wk 27`, `101 Sales 2010`.`Wk 28`, `101 Sales 2010`.`Wk 29`, `101 Sales 2010`.`Wk 30`, `101 Sales 2010`.`Wk 31`, `101 Sales 2010`.`Wk 32`, `101 Sales 2010`.`Wk 33`, `101 Sales 2010`.`Wk 34`, `101 Sales 2010`.`Wk 35`, `101 Sales 2010`.`Wk 36`, `101 Sales 2010`.`Wk 37`, `101 Sales 2010`.`Wk 38`, `101 Sales 2010`.`Wk 39`, `101 Sales 2010`.`Wk 40`, `101 Sales 2010`.`Wk 41`, `101 Sales 2010`.`Wk 42`, `101 Sales 2010`.`Wk 43`, `101 Sales 2010`.`Wk 44`, `101 Sales 2010`.`Wk 45`, `101 Sales 2010`.`Wk 46`, `101 Sales 2010`.`Wk 47`, `101 Sales 2010`.`Wk 48`, `101 Sales 2010`.`Wk 49`, `101 Sales 2010`.`Wk 50`, `101 Sales 2010`.`Wk 51`, `101 Sales 2010`.`Wk 52`
FROM `F:\Reports\Misc\2010\Access Reports\Flair Reports 2010.accdb`.`101 Sales 2010` `101 Sales 2010`





I apologize if this is a similar post

I have exported from from my Accounting Program into Excel a list of customers and their sales from Sept. 2009 into one worksheet and customers and their sales from Sept. 2010 into another.

I have included the spreadsheet for your review.

Can anyone help me with either a macro or formula or function that can take information from both sheets and in the third worksheet create a comparison in the following format (columns)?

Customer Name | Last Year Sales | This Year Sales | Difference | Percent Change

The trick is..... the customer list may be different from one period to the other, but they need to be included if they had activity or not in either year.

Thank you in advance, I have been racking my brain for weeks around this issue. Sure I can do it manually but it would take me at least 30min to an hour to sort and organize it.

Gracias!!!!


I have my sales record at the excel and at the end of the year i want to find the total sales customer wise so that i can know the purchase ration of each customer.
My record is like this

Date Customer Amount
2009/1/5 Ram 1000
2009/1/5 John 200
2009/1/6 Ram 210
2009/1/6 Kim 20
2009/1/8 Ram 100
2009/1/8 John 500
2009/1/9 Micheel 50
2009/1/9 kim 40

I want to solve this like this

Date Customer Amount Customer Total Sales
2009/1/5 Ram 1000 Ram 1310
2009/1/5 John 200 John 700
2009/1/6 Ram 210 Kim 60
2009/1/6 Kim 20 Micheel 50
2009/1/8 Ram 100
2009/1/8 John 500
2009/1/9 Micheel 50
2009/1/9 kim 40


Please help me to sort my sales report.


Hi, I have the following set of data:

name date Sales $ Units alex 1/25/2009 500.00 1 amy 5/28/2010 3,000.00 5 brad 5/29/2010 65,468.00 7 jodh 5/30/2010 54,687.00 8873 kris 5/31/2010 6,577.00 875 erik 6/1/2010 487.00 8 james 11/25/2008 87,647.00 90 alice 11/26/2008 787.00 4 bob 11/27/2008 877,964.00 55 jay 11/28/2008 687,687.00 57 erika 11/29/2008 7,777.00 547

What's the best formula to use to pull up, say, the top three sales persons based on the sales?

Thanks


We have a very simple set of data that we collect each week from each store. See http://rosewood.shackspace.com/Sales...%20Sample.xlsx

We put this into a Pivot Table using Weeks for our rows, StoreNum-StoreName-Year for our Column Lables and for Values we just use the SaleAmmount (actually the Sum of SaleAmmount). This allows us easy data entry but a good way to view the sales data.

What we need to start doing is comparing sales. That has been easy enough in that we can just add %Difference From into the Values and tell it to show the difference from the previous year, 2009 in this case. This kind of works but this is where I'd like to do some tweaking.

We would like to filter out any sales data from 2010 where there is no sales data for that same week in 2009. For example if a store opened in week 10 of 2009, in the 2010 report we don't want to see the weeks 1-9 of 2010.
We would also like to filter out any sales data from 2009 where there is no sales data from 2010. For example we are in week 39 right now so we don't have weeks 39-52. So we want to filter out weeks 39-52 (but once the week 39 data is in, then week 39 from 2009 shows up).

Any ideas or suggestions? We are using Excel 2010 and have access to Power Pivot.

Looking at the Sample Data, http://rosewood.shackspace.com/Sales...%20Sample.xlsx

Sheet 1 - Some raw sample data
Sheet 2 - Our basic sample sales pivot that helps see the three examples we are working with. One store that was open all of 2009, one store that opened in week 11 of 2009, and one store that has only been open for a few weeks of 2010
Sheet 3 - What I have so far on the Growth Chart and notes on what I'm trying to change
Sheet 4- What I want is a manually modified version of the Growth Chart and notes on what is differentBy the way, the data is actually stored in an MS SQL db

So if there are better tools than Excel's pivot table (like the SQL Reporting tool) that can do this, then I'm all for it.

Off the top of my head I was wondering if there is some way to structure the SQL query or design a SQL view such that the data we don't want isn't pulled. But I have no idea how such a query would be structured.


Thanks!


I've got a pivot table that shows Customer sales by item by Region. I'm trying to show a single Customer's sales against all other sales. I can do this with a couple of calculations outside of the pivot table, but I'm curious if this can be done within the pivot table itself.

For example, let's assume there are 4 Customers in Region 1.

Customer 1 sales = 100 units
Customer 2 sales = 200 units
Customer 3 sales = 150 units
Customer 4 sales = 400 units

What I'd like to do is show Customer 4 sales of 400 units against the aggregate of his competitors (in this case 450 units). Likewise, if I were to select a different Customer, I would want to see their sales compared to the aggregate of his competitors.

Possible?


I'm trying to figure out how to have two calculated summary rows in a pivot table. (The default is a sum summary total.)

My data table has the following columns:

- Group
- Amt Spend
- Category
- Year (values of 2008 & 2009 only)

My pivot table has the Group & Year as the row labels and the category as the column labels with the Amt Spend summed as the data.

As mentioned, I don't want to show the subtotals (I figured out how remove those); however, I do want to show two summary lines for each group showing:

1. the amt spend difference between 2008 & 2009
& 2. the % difference of spending for 2008 & 2009.

If it's not possible to have 2 summary lines, I would rather have the amt difference.

Any help?

FYI - I do see that I can select (Summarize Data By > More Options > Show Values As > Difference From); however, that changes all data values, not just the subtotal line.

Thanks.


I have two seperate spreadsheets, the first has columns for 2009 sales Period 1 -12 by customer (the customers are the rows) the second has the same data for 2010. The only catch is we might have more customers in 2010 vs. 2009. I need an easy format that our sales manager can see year over year sales for each customer.

Any help would be much appreciated.

Thanks,


Hi all,

I need difference between two times

Sheet1

  A B 1 Rec DU 2 11/6/2009 9:16 11/6/2009 11:12 3 11/6/2009 10:24 11/6/2009 11:15 4 11/20/2009 13:10 11/20/2009 14:22 5 11/25/2009 14:36 11/25/2009 15:55 6 11/27/2009 9:04 11/27/2009 11:02 7 2/14/2009 6:30 2/14/2009 10:12 8 12/8/2009 6:30 12/8/2009 11:17 9 12/7/2009 16:00 12/8/2009 9:07 10 12/8/2009 15:07 12/8/2009 16:22 11 12/9/2009 11:49 12/9/2009 13:29 12 12/10/2009 8:01 12/10/2009 12:07 13 12/11/2009 14:11 12/11/2009 16:22 14 12/15/2009 12:07 12/15/2009 14:10 15 12/15/2009 10:10 12/15/2009 12:22 16 12/16/2009 10:17 12/16/2009 11:36 17 12/17/2009 11:45 12/17/2009 13:14 18 1/4/2010 12:55 1/5/2010 8:50 19 1/5/2010 11:53 1/6/2010 10:01 20 1/6/2010 10:00 1/7/2010 7:38 21 1/7/2010 11:07 1/7/2010 11:55 22 1/8/2010 16:47 1/11/2010 9:02 23 1/12/2010 9:00 1/12/2010 11:08 24 1/13/2010 13:24 1/18/2010 8:54 25 1/14/2010 13:36 1/14/2010 14:11 26 1/18/2010 10:46 1/18/2010 13:25 27 1/19/2010 14:12 1/19/2010 14:45

Excel tables to the web >> Excel Jeanie HTML 4


Looking for results in hour.

(Example 2.00 , 1.13 , 1.36)


Hi everyone,

I have got a form in which I have got some fileds. The form as of now is not connected to any table. I have got 2 tables also which are not interconnected to each other. These tables have some common values in 1st column only. The common column is Customer's names column(1st column)

1st table 1st column has got Customer name 2nd column Customer Address and 3rd column amount spent by customer in year 2007-2008.

2nd table 1st column has got Customer name (may or may not be same as 1st table) 2nd column Customer Address and 3rd column amount spent by customer in year 2008-2009.

What I need here to make a form in which we can search for Customer's name. The customer name should be searched in both Table 1 and Table 2.
The result window then should come out with Customer's name, his address and the amount spent by him in both 2007-2008 and 2008-2009 years and the total amount spent by him from 2007-2009 (addition of both 2007-2008 and 2008-2009).

If the customer is present only in 2007-2008 then 2008-2009 box can be empty and vice-versa also.

I hope I have made myself clear also. Please revert in case of any questions.

Please suggest me how to proceed here as I am very new to Access.

Thanks in advance for your help.


Hello,

I am not very familiar with Pivot Tables but the following should be quite obvious to do, I just do not know how .. this is where you guys come in, hopefully^^

I've PT:

========>Column Labels:
========>Year 2010 ================ ||Year 2009
========>Net Sales / Qty / Margin % === ||Net Sales / Qty / Margin %

RowLabels:
Customer1
...

I would like to add 2 "Column Labels":
1. That calculates the in Qty from 2010 versus 2009
2. The of Margin% from 2010 versus 2009

Anyone who can explain me on how to do this? (Excel 2007)
Thanks in advance, Sige


hi all,

this is my first post. kinda ran into an issue. can anyone help me?

im trying to get the max from column e for each season. the values in column c correspond to months.

winter 12, 1, 2
spring 3, 4, 5
summer 6, 7, 8
fall 9, 10, 11

how do i go about doing this for each year?

thanks to all for helping a first timer!

2008 2008 1 A 1,845
2008 2008 2 A 1,776
2008 2008 3 A 1,628
2008 2008 4 A 1,587
2008 2008 5 A 1,907
2008 2008 6 A 2,486
2008 2008 7 A 2,829
2008 2008 8 A 2,703
2008 2008 9 A 2,289
2008 2008 10 A 1,665
2008 2008 11 A 1,624
2008 2008 12 A 1,880
2008 2009 1 A 1,879
2008 2009 2 A 1,812
2008 2009 3 A 1,664
2008 2009 4 A 1,628
2008 2009 5 A 1,957
2008 2009 6 A 2,548
2008 2009 7 A 2,897
2008 2009 8 A 2,765
2008 2009 9 A 2,355
2008 2009 10 A 1,713
2008 2009 11 A 1,679
2008 2009 12 A 1,925


Hi,

Here's what my table looks like:
Sum of Sales |Year
Region |SalesRep |Type |2009 Sales |2010 Budget
1 |John |Product1 | 1,000 | 1,100
|Product2 | 100 | 200
|Product3 | 10 | 50
|John Total | 1,110 | 1,350

and I have more than 1 region and more than 1 rep per region.

I would like to perform some calculations and have the table shows as below:
% Inc.= (2010 Budget)/(2009 Sales)-1
Sum of Sales |Year
Region |SalesRep |Type |2009 Sales |2010 Budget |% Inc.
1 |John |Product1 | 1,000 | 1,100 | 10%
|Product2 | 100 | 200 | 100%
|Product3 | 10 | 50 | 400%
|John Total | 1,110 | 1,350 | 21.62%


I know I can use Summarize value by % Difference From, but the pivot table only displays the result % and the actual sales figures disappeared.

I tried to insert a Calculated Field Formular = Year[2]/ Year[1]-1 but Excel won't accept it.

Please help!


Hi all,

I want to compare A&b = D&e

Sheet1

  A B C D E 1 Start date end date Compare Start date end date 2 21-08-2008 21-08-2010 Match 21-08-2008 21-08-2010 3 22-08-2008 22-08-2010 Match 22-08-2008 22-08-2010 4 23-08-2008 23-08-2010 Match 23-08-2008 23-08-2010 5 24-08-2008 24-08-2010 Match 24-08-2008 24-08-2010 6 25-08-2008 25-08-2010 Match 25-08-2008 25-08-2010 7 26-08-2008 26-08-2010 Match 26-08-2008 26-08-2010 8 27-08-2008 27-08-2010 Match 27-08-2008 27-08-2010 9 28-08-2008 28-08-2010 Match 28-08-2008 28-08-2010 10 29-08-2008 29-08-2010 Match 29-08-2008 29-08-2010 11 30-08-2008 30-08-2010 Match 30-08-2008 30-08-2010 12 31-08-2008 31-08-2010 Match 31-08-2008 31-08-2010 13 01-09-2008 01-09-2010 Match 01-09-2008 01-09-2010 14 02-09-2008 02-09-2010 Not Match 02-09-2008 02-09-2011 15 03-09-2008 03-09-2010 Match 03-09-2008 03-09-2010 16 04-09-2008 04-09-2010 Match 04-09-2008 04-09-2010 17 05-09-2008 05-09-2010 Match 05-09-2008 05-09-2010 18 06-09-2008 06-09-2010 Match 06-09-2008 06-09-2010 19 07-09-2008 07-09-2010 Match 07-09-2008 07-09-2010 20 08-09-2008 08-09-2010 Match 08-09-2008 08-09-2010 21 21-08-2009 09-09-2010 Match 21-08-2009 09-09-2010 22 22-08-2009 10-09-2010 Match 22-08-2009 10-09-2010 23 23-08-2009 11-09-2010 Not Match 23-08-2009 11-09-2012 24 24-08-2009 12-09-2010 Not Match 24-08-2009 12-09-2012 25 25-08-2009 13-09-2010 Not Match 25-08-2009 13-09-2012 26 26-08-2009 14-09-2010 Not Match 26-08-2009 14-09-2012 27 27-08-2009 15-09-2010 Not Match 27-08-2009 15-09-2012 28 28-08-2009 28-08-2011 Not Match 28-08-2009 16-09-2012 29 29-08-2009 29-08-2011 Not Match 29-08-2009 17-09-2012 30 30-08-2009 30-08-2011 Not Match 30-08-2009 18-09-2012 31 31-08-2009 31-08-2011 Not Match 31-08-2009 19-09-2012

Excel tables to the web >> Excel Jeanie HTML 4


I need answer in c.

I am looking for vba code...