I'm using excel 2010.
I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.
In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?
Excel Help.xlsx
Excel 2010 windows 8
I am in need of a formula to count filtered results. On Sheet1 (Job Flow) the user enters data continually . The most important is the Date in column D some measurements in columns N,O & P that gives a result as a code, example s15020 or c3005 in column Q. A vlookup is then pulls up a price for the code.
On sheet two Column A There is a list of all the codes. I need a formula that will give a count in column B for the amount of codes that was entered in to the data base "sold" for a date filtered time period, be it a week, month or year. In other words the codes in ,column A sheet 2, should reflect the amount of units for the time filter on page1.
I am trying to set up a simple sports picking list using Excel 2010. I would like to be able to carry out several filers and then extract results to a new sheet to allow printing.
For example, there are four available terms and I would like to be able to filter by term/sport and student. Ideally I would like to be able to add a command button once this is working.
I have a worksheet with
Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)
There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.
I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.
For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.
I am using Excel 2010.
Name
Primary
Secondary
Tertiary
Person 1
x
[Code] .........
I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.
View 2 Replies View RelatedI am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.
I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".
For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.
The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.
There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.
Code:
ActiveSheet.Range("$A$1:$AM$14502").AutoFilter Field:=1, Criteria1:= _ "30/06/2013"
When I run the above on my Worksheet to Filter for Rows not equal to 30/06/13 this works fine
I want amend the Criteria1 to a string that is derived off a value in my worksheet I have done this as below where Range("D8").Value is 30/06/13
It is deleting al my data and not keeping the rows with 30/06/13 in it.
Code:
Dim rng As Range
Dim LastQtrDate As Date
Dim LastQtrDateString As String
LastQtrDate = shtControlTab.Range("D8").Value
LastQtrDateString = "" & LastQtrDate
[Code]...
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies View RelatedWhat I am trying to get is the distinct number of cost codes based on the budget codes.
Basically, when I do a regular pivot, I can see it gives me the 'COUNT OF CODE CODE' = 4 in the summary section, but, that seems to give me the number of records per Cost Code which is not what is needed but rather I would see a distinct # of codes codes based on budget code, and to boot, the subtotal should be directly under the Cost Code column.
Is that even possibly just using the pivot tools available in excel?
I have several macros that join spreadsheets and then sort and subtotal them. I now need to use my spreadsheet that has the subtotals and place only the subtotals into a new workbook. For instance the new file should contain the name of the item and "subtotal" for that item, and the list of all the data should remain in the original file.
View 9 Replies View RelatedI know that there is a way to remove or add subtotal for different fields, how to add subtotal only for the fields that have more than 1 value? I don't want to to subtotal for anything that has only 1 value.
View 2 Replies View RelatedUsing Excel 2013.
Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?
Ex
Group 123
Group 456
Are both members of NorthDivision
If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.
Output:
Group 123 0.75
Group 456 0.25
NorthDivision 1
At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1
Is there any way to outline excel data automatically
In the attached excel sheet : test outline.xls
I need automatically Because my data very large ...
Using Excel 2003.
I have a spreadsheet that summarizes all of our assets in cell C1947: subtotal(3,D2:D1944)
In column G, I have a "Y" or "N" or BLANKS "". I'm trying to come up with a formula that would give me the correct totals for the Y's or N's or blanks when I select one of the AutoFilter columns. For example:
I currently have 1,943 assets. Column G has the Y's N's and blanks. This is showing all the assets. If I select column F which is a specific location, I would like to see the correct count of Y's N's and blanks while the subtotal command shows the total assets.
I have 200 rows of data, each row is associated to an image. I woud like the filters to recognize that the image is associated to that row. I know that image is not "strictly" data. Does anyone know a way to do this?
View 3 Replies View RelatedI am using Excel 2011 for Mac.
I am creating a workbook with multiple worksheets, one overview sheet and then one sheet for each month of the year.
In the monthly sheets I have a Category column for which I have created a drop-down menu of expense categories. Then I have an Expense Amount column where I enter the amount spent.
The Category column will not necessarily remain sorted by category because I will be entering the expenses as they come up throughout the month so the categories will be all mixed up, unless I manually sort them.
What I want to do is somehow automatically calculate a running subtotal by Category (that will update with each new entry) and simultaneously automatically transfer the running subtotal for each Category to a specific cell on the overview sheet.
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedI am doing a project with these instructions:
The state, capital and population of all states with a population greater than 10 million or less than 1 million. (population)
The state, tree and bird of all states with a bird or tree that starts with Eastern or Western (symbols)
The state, population and area of all states with area between 100,000 and 200,000 (area)
I am having troubles with selecting just the three criteria (state, capital, and population) on my Population worksheet. Attached is my work. The copied filter keeps copying all of the data in the data set, and not those three.
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
I'm having trouble filtering a large list of 900 names to create mailing labels for anon-profit organization. For years I have been using Microsoft Works to create these labels, however, the people now doing the membership insist on using Excel. I have Office 2013 and am using Excel 2013 on a new Dell PC using Windows 7. I have partially solved the problem but have one hang up.
I got to a point where I could filter out the dates and a couple of other items, but can't seem to get the last two. I was able to get the minimum date (equal to or greater) than 2012 to filter and the (equal to)LIFE (life membership) one and the (equal or greater than) ID# of 9000to filter by changing the cell format in all of those columns of cells to "text" instead of "general", BUT I still can't get it to (be blank) for the M column which houses a "D" or "U" (indicating deceased or uninterested) and an E column which houses an "E" if the person receives the newsletter electronically rather than by mail.
My fieldset up is:
To Year is equal or greater than 2012
Or M Class is equal to LIFE
Or ID# is equal or greater than 9000
And M is blank
And E is blank
The first three work but the last two do not seem to filter properly.
I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-
ABC
http://192.168.44.29:2967
DENIED
BCD
https://d31qbv1cthcecs. atrk.js
DENIED
[Code] ........
In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://95.138.166.181 is public IP and we need to copy to other sheet.
I have a set of data points with date and time shown in the picture. I need to take out all the data point that are at the 30 min interval. Meaning I only want to have the data points hourly only. What can I do to remove the :30 minute datas.
I have tried the find option typing In :30 and getting all the points but then have to replace, which I do not want to do. Need to remove them so they do not show up on the trends ( graphs) I made for them.
This is for excel 2007.
Seems I can't get picture up on the ipad, so the data points look like this
1/1/2012 0:00
1/1/2012 0:30
1/1/2012 1:00
1/1/2012 1:30
And so on for an entire year. Just need to remove the ones with the :30
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
Is there is some way to filter based on the value in a specific subcolumn.
Using the example of a list of salespeople and their transactions over the year, who sell multiple products, the PT is Sales Person name for the Row Labels, and Type of Product for the columns. The resulting PT has 3 columns, for each of the products - e.g. table, chair couch, and the Values are the total number of that item sold. Is there any way I can filter, so that I will see only those sales people who have sold 3 tables or more lets say. (Thus enabling me to quickly see what other products those sales people have sold.)
(The actual situation is a lot more data heavy than that - it is actually a list of donations for a non-profit, coming in from hundreds of people, across a dozen different categories; I am trying to analyse the extent to which people who gave for a particular category (Direct Mail solicitation) also gave across other categories.
So far I've manipulated things by inserting a '% of row total' value into the PT and then using countif/sumif functions outside of the PT to figure out how many people donated solely to this category (= 100% of row total), and how many donated to other categories too (= more than 0% of row total, less than 100%), which gives me a decent summary of sole donations to this cause vs other categories too, but doesn't visualise what the actual other categories donated to were. I've also investigated making a PT of the existing PT, but I'm not sure if that's even possible...)
(I'm using Excel 2011 for Mac, but if there's some other version that would make this possible, I may be able to use another computer.)
I have multiple invoices for ordered products style number and quantity, these invoices are keep adding as orders are coming. I need a separate work book where all items summed. shows style and summed quantity from all invoices.
for example wb1
style quantity
or12 5
or34 2
or45 4
[Code]...
wb3 same style numbers but maybe not in the same column or row, wbs are keep adding as orders are placed.
I need a separate wb
shows style and pluses all quantities
style quantity
or12 6
or34 5
or45 6
I have built a pivot table that shows parts used during a period of years. I need creating a formula that would sum each part by a particular year.
The table is as follows
A part number
B date
C total quantity
I want to display the totals on a separate sheet.
formula on the attached sheet. I need to update it now. The strengths are going to stay the same, but the weaknesses (purple headers on spreadsheet) only have to show up if the value associated with it is 3 or greater. (So traits with quantities of 1 and 2 will not show up as a weakness)
I have attached a spreadsheet wit desired results.