1. I would like to be able to play with it by choosing different categories, sub-categories etc (adding different ones etc.). I tried pivot table but to get this data in pivot table format is a very time consuming task to start with.
2. The sample file has only one month. I get this type of data every month. So have to annualize using other months etc. I could get different months in a column also. So for example column d could have Jan, Feb, March etc. for row of data. But that would mean I will have 1922 * 10 = 19220 rows. And when I update this every month row reference from formulae (If I had formulae) will need to be updated.
I have the flexibility of getting this source data rearranged little bit but not too much flexibility there.
I am working on my thesis empirical part now I have 10 years from 2003 to 2012 and need to divide this into 2 categories pre-cisis and post-crisis. How can I do that?
I have a given set of data. Each row of data fits into one of 7 different categories. Besides doing a simple filter to be able to pull out a certain "category", how can I do this differently? Is there a code/macro process I need to go through? Basically what I need to do is find a way to group these categories together, so that someone searching for a certain category can easily pull it from the data.
I have two columns, in column A will have four or five categories repeating randomly, in columns B will have unique names.
What I would like to do is to define categories as a column names in a table and then to automatically fill that table with the data from column B depending on category.
I extracted data from PDF into a spreadsheet, and now I need to put all the data - according to name, email, address etc. into separate columns, so it is usable.
All categories of data are in 1 column at the moment - mixed up.
How can I sort this into columns according to category?
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
my spreadsheet attached, when you open it up you will see the tabs on the bottom, each of those tabs are departments.
This spreadsheet track all the jobs coming through my department, placed by other departments.
On the right hand side of each sheet are print numbers columns, and total hour columns.
at the end of a job I record how many hours the job took or how many dollars it cost. Then the job is close out and that line of information is sent to the archive page.
The archive page is where all the information is stored, print numbers hours and dollar amounts and so forth.
what I would like to do is, set up another sheet within the workbook that would sort all of the part numbers and associate them with hours or dollar cost.
I'd like to have it start the search when a new job is entered and I put the print number into a cell, in any of the department pages.
I've have a spreadsheet where 4 cells are linked to another workbook via a vlookup. the problem i have is that a lot of users can update this external book, or it can be saved as a seperate spreadsheet somewhere else on the network. If it was up2 me i would have them only update the one sheet, but as it stands its not. So what i want to be able to do is put some code onto a button on the sheet, from here i want the: Application. GetOpenFileName
method to open....but from here i want them to be able to pick the cells where the data is situated. Any clues..... i can get as far as them selecting a workbook. Do you think i will need to create another userform?? Maybe RefEdit? I'm not sure.
when you choose Data Validation for a specific cell, you can choose list. Once you have chosen list for "Allow", you can select the source cells for what will be in the drop-down menu. I want to be able to use VBA (or just use Excel) to modify those source cells.
For example:
There will be 3 lists with drop-down menus using Data Validation.
List 1: Choose first option in list -> List 2 uses source A Choose second option in list -> List 2 uses Source B etc...
List 2: Choose first option in list -> List 3 uses source C Choose second option in list -> List 3 uses source D
I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.
What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example
1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.
What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?
Sheet is attached.
I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.
I have a spreadsheet listing various pieces of computer hardware, software and mobile accessories, etc with costs attached.
I have created a dropdown list of staff profiles eg a sales representative, finance assistant, IT administrator.
What I want to do is get Excel to pull the data through from the data sheet, to a printable summary output form (something you could also cut and paste from). For example, a sale rep would need to be set up with a laptop, laptop case, mobile phone, etc, so when you select Sales rep from droplist, I want that action to pull all the associated data through to the summary form. Similarly, a Finance assistant would require different kit , perhaps a desktop PC , monitor , etc.
Next to my data I have created columns with "Y" for yes in cells containing kit that match the staff role requirements, but I am not sure of the best way to pull this together.
We work in a Work book with Various sheet.. in this workbook A master sheet (masterdatasheet) & many other datasheet (Each sheet with diff data)
We want a VBA for my master sheet with a Combo box or list type data validation.. where we choose Sheets Name ......... after choosing datasheet... those sheet data Dispaly in My Master data sheet....
with MasterdataSheet we are caclucating some calculation which is which is sown in bottom line in this sheet
My Sample file is attached with 2 datasheet.. (in my original sheet more then 50 sheets so we enclosed only 2 sheet for trail baisis)
(My Hiden sheets protection password is san19241 or san if u want to open those files).
Could somebody please suggest a formula that will sum values at each change in category?
I've played around with a pivot table, but for this it really doesn't fit.
Column A is the category and there are totals in column B for each entry.
The only place I need a formula is at the change in First and Second...so for any second all of the third's plus that second and for the first everything below.
Is it possible to make a ComboBox with categories. I have provided a sketch below of how I want my ComboBox to look like. I don't want the user to be able to click on "Fruits" or "Vegetables", but they should be able to select any of the other options.
The ones we need to focus on are Columns A,C, H, and AG. Daily this sheet ‘Raw’ will be filtered and data sent to sheets 100,200,300.
In the Raw sheet we have many rows of data, but when this is transferred to the other sheets we will only see one row of data in each sheet.
1. The Date from Cell(C2) should be copied to Cell ‘A3 or next empty cell on column A’ on all 3 sheets(100,200,300)
2.In Column A there are 3 accounts – 101,201,301 If Column A value = 101 then send the corresponding Column H (P&L) value to Sheet”100”, If Column A =201 then send to sheet ‘200’, If 301 then to sheet ‘300’
Now that’s not quite what I need, the Column H values in some cases have to be added up – by category. You can see this on sheets 100 – 300. Columns B to K on Sheet(100) have the different categories of products. What I want is a code that would go through Column AG in Sheet(Raw) match the category name to the category name in the Range(B2:K2) and copy the P&L figure (Column H value in ‘Raw’) there.
e.g. I need all the rows pertaining to account 101 with the same label ‘Interest Rate’ to have the P&L (Column H) added up. So I would get the value 188760 in Sheet ‘100’ Cell D3. (I just added H2,H3,H12,H13) ...
I want to average cells in different columns which meet a minimum criteria, i.e. > 2.0, from specified categories list in the first column. Example: I want to select a category listed in column A and average the numbers listed in columns C and F for that category which meet a minimum threshold.
How do I summarise the attached table. I want to have a drop down menu in the MONTH cell (B13) that summarises the month nominated. Also a drop down menu in CATEGORY that I choose (B14). The value of the category in any particular month is totalled in B15.
I know how to create drop down menus and how to define ranges - just not sure of the formulas to use in order to get the correct answer in cell B15....
I have a series of up to 40 or so entries for given dates. These entries are categorised by either "bid" or "offer" and I'm trying to find a way to chart them both using a pivot table. I'm stuck as it seems that a pivot can only count the number of bids and offers on any given day, but it cannot differentiate between them.
Note: that the original list the pivot depends on will constantly be updated so I can't just develop a custom chart.
I've attached a spreadsheet with an example of what I'm playing with. Pivot graph is in "PIVOT" worksheet, data is in "DATA".
I have a file and it has a table on it with 2 categories.
what i want to do is select a color in the first drop down list and than in the second only select the items that match the color i have previously selected.
Is there anyway to do this with named ranges because i would like to index everything in a table.
I have a small online business and am slowly learning Excel to keep my records. I looked at Quickbooks and I think that it just a little too complicated for my needs, besides I like excel better.
The spreadsheet I want to make is how can I summarize the different categories, shipping, travel, EVSE, Wire, or whatever I come up with in the future from a daily expense spreadsheet. I guess the summary should be on another page.
I also guess I can make up a total also of the companies I buy from...
I've attached a beginning daily expense spreadsheet with some entries.2014 costs.xlsx
I have a column of numbers that look like this: 111222111222111 and I need them to look like this: 111 222 111 222 111. The numbers are all random and not 111222.
Then I have a second column with numbers like this: 11112222111122221111 but they need to be like this: 1111 2222 1111 2222 1111.
I am building a tracking list for several items and have categorized them
Software Hardware Networking
I will have several of these and the list keeps getting bigger. I have a table above the list that summarizes these catergories and the count of them. How can I count each categorie and so that it updates each time I add one.
The formula would have to sumproduct or determine what categories are there and how many (unique list) and update when I add one.
Currently its a manual process of creating a unique list and doing a countif.
I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
Drop list. First of all I have sheet with all my data and i have define the name of each table.
I have about 15 categories table and a table with min 2 and max 100 items in each category.
I have made in a different sheet a drop list for my categories. Near this list i made another drop list with the items by using the formula =indirect() to arrange my items by categories.
The problem that i have is: - Some categories they have many items and is difficult to find some items. i need to put a formula to put some letters and find what i need easily (easy searchable).
Formula that can calculate % of total for individual items. For example,
Item NoCategorySales% of total 1234Bed150 5678Bed100 1547mattress52 58658mattress188 12356bed12 8954mattress185
I need to find out each item % of total per their own category. For example item 1234 should equal 150/(Total Bed) to get % of total per category and item 1547 should equal 52/(Total of mattress)
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.
