Excel Table to Individual product list and spend

Hi, Basically I have an Excel data table and I have a Product name
drop down list. I want to create a formula or macro so when a Product
is selected, it would output the list of companies and their spend
with the product.
Below is a sample but the real spreadsheet has thousands of vendor
names and spend for each product and there are probably ten product
names altogether.


Company 	Product1	Product2	Product3	Product4	Total
ABC	 7,009 		 8,000 		 15,009
Key		 805,036 			 805,036
PC	 56,016 	 57,470 	 568 		 114,054
Water	 3,090 	 651 	 298,250 		 301,991


Total	 66,115 	 863,157 	 306,818 	 -   	 1,236,090



There will be a drop down list of the Product names and if Product 1
is selected, below is the output result. A pivot table does not work
because it shows all companies and I only want the companies that has
spend to show up.	Thanks in advance.

	Company	 Spend	%
	PC	 56,016 	84.73%
	ABC	 7,009 	10.60%
	Water	 3,090 	4.67%

Total Spend for Product1 66,115
0
dd2quest (3)
5/28/2008 5:53:54 PM
excel 39879 articles. 2 followers. Follow

2 Replies
773 Views

Similar Articles

[PageSpeed] 52

You can do it with a Pivot table.

You set up your data like this
      Company Product1 Product2 Product3 Product4 Total
      ABC 6338 2086 1272 3807 13503
      Key 7233 1165 7663 5012 21073
      PC     0
      Water 6467 6316 9889 6181 28853



with a column named Total where you sum the sales ("spend") of each company.

The PC Company has spent no money so Total is zero.

You make the Pivot table
Rows :
    Total            and
    Company

Columns :
    Nothing in the Columns

Data :
Count of Companies

OK, OK, OK.
Now you have the Pivot table


Now, in a cell of totals,
Right click,
Group

Xl wants to start at zero as the lower limit but replace the zero with 1, 
(or 0.01),
put a big number like  10 million as the grouping factor.
OK

Now Xl will produce two groups
1. all those with <1 (that's zero spend).
2. all the others, those between 1 and whatever

Right click in the second group and Mask it.

You're left with the lower than 1, probably zero.

is that OK ?

R.


0
raoul (6)
5/30/2008 2:16:27 PM
Sorry,
do use the Pivot table as I propose above, but at the end, mask the other 
group, i.e. the less than 1 group.

R.


0
raoul (6)
5/30/2008 2:18:18 PM
Reply:

Similar Artilces:

table of contents with multiple entries
I have a table of contents that I've made using code found on this site. However, several times in my report, a person's name is repeated and I'd like to include both pages in the table of contents. John Doe.................1, 23 Any suggestions would be appreciated. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200704/1 Presumably you have set up a table where you record the customer's name, and the page number. And you use the Print event of the (Detail?) section of your report to populate this table. And then you ...

the criteria pane and table panes have disappered.
In my excel file I can't see the criteria and table panes and the functions will not reappear. ...

Pivot Table not valid error message when formatting data 'button'.
Created Pivot table/chart, password-protected and put onto website (ie NOt saved file as Web Page). Click on link, get warning message concerning macros and prompted for password. If attempt to change data button by rightclicking on it and selecting format - get error message 'Pivot Table not valid'. Note, accessing the file directly (ie not via the web) works perfectly Ok. PS Also get visual basic error message ' Run time error 1004 :Unable to set the default property of the Pivotitem class' VB code Sub All_Years() CP = "(All)" pf = "Year First Seen"...

excel VBA
How would i search a range of a whole column instead of specific cells like .Range("a1:a300")? --- Message posted from http://www.ExcelForum.com/ ..range("a:a") ..columns(1) ..range("a1").entirecolumn are a few ways. "Zygoid <" wrote: > > How would i search a range of a whole column instead of specific cells > like .Range("a1:a300")? > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ahhh! Thanks!! I was trying .Column(1) i see i neede an "s" in there Thanks a...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

"Do you want to save" when Excel OPENS
I understand about the "Do you want to save" message when you close Excel, even if you didn't make any changes: Excel doesn't distinguish between real changes and the recalculation of volatile functions. But at work we've had a user claim that he gets the message when he OPENS Excel when our add-in is checked (having been checked when Excel was open before). Specifically, it asks "Do you want to save changes to Sheet 2?" I'm sure it's not our add-in as such, since no one has ever report this before. But it may be some interaction between our add-...

Junk E-Mail Lists Error in Outlook 2003 with Exchange 2003
I have seen several people post about this error message that comes up in Outlook, but so far I haven't seen anyone respond. I am running an Exchange 2003 server with SP1 and my client is Outlook 2003. I installed IMF a few weeks ago, and it appears that this message started soon afterwards. I have now uninstalled IMF, but am still receiving the message. My end users are getting frustrated, as am I. There also appears to be an error in the IMF deployment guide about adding a registry key to increase the list size in Exchange. It states to "right click System", but there is n...

Excel Time Problem
I exported a file from a telephone switch reporting system as a "tab separated text" file, because when I export the data in Excel I have to deal with merged cells and other formatting problems. I have a couple of columns that have time values like 160:02:05 for logged in time, and 34:16:46 as the waiting dime� Both are time �DURATIONS� or time in a state. I used the Format, Cell, custom and [ss] to get the duration value to seconds, and I get 123406 and 576125 respectively. I then use these numbers in a calculation typically like 1338 / (576125 - 123406) and I get 219 as the an...

Excel 2000: File >> New menu command causes application crash
This is occurring on only one machine on a network! After saving an emailed Excel template to a standard network location for Microsoft templates, when selecting File >> New this installation of Excel 2000 crashes ("Excel not responding" on Close program dialog). On other machines there is no problem. Have "repaired" Microsoft Office using that option from the installation CD-ROM, have removed Excel from Office installation, rebooted and reinstalled Excel. No change on the problem behaviour. Everything else appears to be working just fine in this copy of Excel. We ...

Spending limit
How can I prevent money (2004) from asking me to set up spending limits. There are NO check marks in the FYI Advisor. ...

Merging tables
Hello, I do not have deep knowledge of Excel. That is why my proble might appear a bit strange: I have two tables, list1 and list2. In both tables there is a colum with unique ID numbers. The rest of the columns contains differen information in both tables. Example: list1 ----- ID name email 101 bob bob@none.com 102 rita rita@none.com ... 999 jim jim@none.com list2 ----- ID salary 102 2000 103 2500 ... 204 2100 What I would like to get as a result is a combined table, with selecte columns from both tables in it. I.e.: list3 ----- ID name salary 101 bob - 102 rita 2500 ... Anyone who can ...

excel #76
i have two different spreadsheets containing two columns, column A contains reference numbers (both sheets will have the same refernces) column B will contain the amounts which may differ on each sheet. i need to run a compare program to identify all amounts that differ between the spreadsheets for the same refernce number. for eample sheet 1 reference no amount 111 120.00 112 345.90 113 122.50 sheet 2 reference no amount 111 0.00 112 345.90 113 100.00 how can i find out the amounts and refernces...

EXCEL.exe has generated errors
For an knowned reason, the following message recently started to display when clicking on EXCEL on an Windows 2000 system. EXCEL.exe has generated errors and will be closed by Windows. You will needed to restart the program. An error log is being created. (OK) Attempts have been made to repair, deinstall and reinstall Office 2000. However, the above message still displays with trying to bring up EXCEL. Can anyone help? This WebPage by Chip Pearson provides a good place to troubleshoot. http://www.cpearson.com/excel/StartupErrors.htm HTH Paul --...

Excel 2007 dates and conditional formats
Can anyone please tell me why I can not use a conditional format (<>=) to identify if a date is greater or smaller then another. Everytime I try it, Excel does it right in the first column but misses by 2 days in all following. It also appears to have problems with the years. Example: Date of propposed compleation of a task is 10/10/07; if the date of actual completion is 10/10/07 or sooner, turn GREEN. If the date of completion is 10/11/07 or older, turn RED. Can anyone help?? Regards Mary Hi Mary, First thing to check: What does 10/11/07 mean? November 10 1907? November 10 2007? O...

Only 1 taskbar button when i open 2 excel documents
Hi all. When i open 2 excel documents i have only 1 taskbar button on the taskbar even though i did not choose the option to group similar taskbar buttons under taskbar properties. Hence when i want to switch between the 2 documents i need to go to window and select them. Hence how do i solve this so that i can have 2 taskbar buttons of the excel documents on the taskbar? Thks in advance. Tools>Options>View, check Windows In taskbar -- HTH Bob Phillips "inenewbl" <inenewbl@discussions.microsoft.com> wrote in message news:6F715432-2EB2-47AC-B737-56D63F37537A@mi...

how do i create a quiz using excel?
hey guys...i badly need to make a quiz using excel,,,i got no clue how to do it. the quiz will be a simple one, no drop downs and should display the scores after the quiz is taken.... please help! Andruu, What type of quiz are you looking to create? What format are the answers? Numbers? Text? One suggestion..... 1. Add questions *What is 2 x 2?* 2. Assign cells for the user to insert their answers. (format cells as text/numbers etc) *Cell: B2* 3. Either in hidden cells, or on a seperate sheet, put the correct answers 4. Assign a point value for each correct answer (can be different for ...

Creating a new document from a multiple paged Excel document
Can I save only one page of an existing Excel multiple page document? If so, how? Thanks to anyone that can take the time to answer/instruct. Right-click the sheet tab that you want, and hit Move or Copy. Choose "Create a copy" and then, from the dropdown, choose "new book". **** Hope it helps! **** ~Dreamboat Excel VBA Certification Coming Soon! www.VBAExpress.com/training/ ******************************** "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:CBBDECC6-2432-41D4-99F2-C1EF10B1EC1F@microsoft.com... > Can I save only one page of...

What does the "E" in 5E
I am used MS EXCEL to perform a scatter plot. When the information was graph, Excel provided me with the formula 5E - 71e^0.085x. What does the "E" in 5E mean? The E is the way Excel represents 10 to the power. So 1.2E3 is 1.2X10^3 or 1200 and 1.2E-3 is 1.2 x 10^-3 or 0.0012. "College Professor" -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "College student" <College student@discussions.microsoft.com> wrote in message news:8F20E8A3-BDC6-42E5-A5D0-508D57C40F42@microsoft.com... >I am used MS EXCEL to perform a scatte...

Pivot Table Refresh Problems
Hi All: Hoping that someone can help. I am trying to refresh a pivot table using the following code which was inserted on the Daily Production Output Sheet(both sheets in the same workbook). I am using Excel 2003 Private Sub Worksheet_Calculate() 'If data on this worksheet changes, refresh the pivot table Sheets("Daily Production Output").PivotTables("PivotTable3").RefreshTable End Sub The Calculation has been set to Automatic. However when I try to run the above code I am getting the following error" Run Time error 1044, Application defined or Object defin...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

Excel 2007 exits without asking if I want to save
Hi All, I just started using Excel 2007 recently. I notice it exits when I keys in Alt-F4, without confirming if I want to save, even if I had made changes to the workbook. Is there an option which I can set and make Excel 2007 verify if the user, wants to save before exiting? This is the default in earlier Excel versions. Thanks for help. have you recently run a macro in which you forgot to turn on Application.DisplayAlerts =3D True? susan On Nov 24, 5:25=A0am, ch <c...@discussions.microsoft.com> wrote: > Hi All, > > I just started using Excel 200...

Scrolling in Excel
Please help My spreadsheet has a 'frozen' row at the top containing column headers, when I scroll down through my rows below, excel scrolls down 3 rows at a time. Now because my data in the rows below is in sections (1 section contains 5 rows) sometimes I end up viewing 2 different sections (half of one and half of the one below) at the same time. Is there anyway you can instruct excel to only jump down so many lines when you scroll down? Many thanks -- Bensum Change the mouse wheel settings in the Control Panel of the operating system you use. Bensum wrote...

get a list of file in a directory
Hi. what would be the best way to return a list of files in a directory. I know in VB you could use the dir function, but what should i use in MFC. Doen anyone know of a class available where I could say return all the file in a certain directory and its sub directories Regards Dylan Take a look at the CFileFind MFC class. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Dylan Franklin" <dyl2000@lineone.net> wrote in message news:eHHBr0MxDHA.2436@TK2MSFTNGP09.phx.gbl... > Hi. > > what would be the best way to return a list of files i...

excel opens under task bar
hi, I find that excel always opens under the taskbar. is there some way to 'reset' the opening position to the screen dimensions + taskbar size so it opens below ? it seems to do this on XP pro and 98 SE from time to time. thanks, kd ...

customizing the task list print options
I'd like to print my "tasks" list but with the complete Note field included. How does one accomplish that? I'm surprised it isn't a standard form provided in Outlook. -- ------------ Michael Bierman ...