I have sales data in columns, the right most column has values in it.
If I filter the data using the standard Filter, I can add only filtered records by using SUBTOTAL(9, filtered_list) and this gives me the answer.
How can I find the 2nd largest or second smallest ONLY from the filtered data without using a Pivot table?
I'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies View RelatedI'm writing an application in Excel, its a little over 5MB after I've run through UsedRegion and trimmed stuff, 94 worksheets. I'm encountering a fair number of crashes these days. My OS is fully patched as is MS Office. Running Windows server 2003 and Excel 2007.
I was wondering about the pros and cons of writing an application in one large workbook or many smaller ones. So far its been one large one but these crashes are really impeding my progress.
What's the best way to remedy these crashes or even start to diagnose?
I have a column of number that I want to use the Small & Large function on, skipping duplicates.
Example:
Column A has the following numbers;
1
6
1
4
3
2
4
6
17
8
21
8
10
13
I want the Small function to identify the first lowest number (1) and then the second lowest number (2), not (1) again.
I wondered if there is a possibility to make this
Range("L3").FormulaR1C1 = "=SMALL(R[-1]C[-11]:RC[-11],1)"
Range("L4").FormulaR1C1 = "=LARGE(R[-2]C[-11]:R[-1]C[-11],1)"
more simple so i can get the range for my small and large formula's variable? What i try to reach here is:
Range("L3") = smallest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
Range("L4") = largest date In Range("A2", Range("A" & Rows.Count).End(xlUp))
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
I have a question about sorting. I would like to sort multiple columns from small to large at the same time. Is this possible? I have over 400 columns to sort so I would rather not do this one column at the time.
Example
A.... B.... C
0.2 0.4 0.3
0.4 0.3 0.5
0.1 0.6 0.1
shoud be
A.... B.... C
0.1 0.3 0.1
0.2 0.4 0.3
0.4 0.6 0.5
I understand how to find the top 5 /bottom 5 values using the large/small functions, but the question I have is: How do I get associated fields. For example I attached a sample sheet with values (Fields: Ticker,TE,Sector,Return). How do I get the Ticker,Sector that the return is for. I'd like to show all the associated fields that relate to the retun, but I can't find the solution on how to do that.
View 3 Replies View RelatedI have a file that has been produced using Statistica software. The file is supposed to have around one million rows, and when exported to Excel the file is about 30 MB. When I open the .xlsx file in Excel 2010 only two pages of data appear. The rows also have weird numbering. The first square of the A column is A1. The second is A3833, the third is A6789, then A8161, then A8162, then A8163, then A8164, then A18070.
The scroll button to the left of the screen is long, as if the document was only two pages long. When the scroll button is click-and-dragged a small beige square appears with a row number. This small square seems to be aware that not all rows are shown as it shows many more rows than are visible to me.
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 have a column A1 that consists of a range of values separated by a comma.
row 1:value 1, value 2, value 3, value 4
row2: value1, value 4
Row 3: value 2, value3
I want to get the unique counts for each value in the filtered list. This one field is not filtered but the others in the table are so I only want the counts of value 1-4 when the entire list is filtered.
I have been able to accomplish this with individual values using the following formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET('1. Respondents'!$C$7:$C$39312,ROW
('1. Respondents'!$C$7:$C$39312)-MIN(ROW('1. Respondents'!$C$7:$C$39312)),,1)),
--('1. Respondents'!$C$7:$C$39312=C4))
This works great other than the fact that I need to count total values of the column in an array.
=SUBTOTAL(2,A2:A100) can be used in combination with any other formula so that when the data is filtered using autofilter it will only count values greater than zero.
I have attached a worksheet with the example.
In the attached workbook subtotal formulas are used in Row1 for sum and row 2 for count.
When the data is filtered using code "DUP" in column D, the subtotal in cell C2 is "4", whereas in the specified range there is a positive value in only 1 cell that is $1050 in cell C99.
Is there some combination that can be used with SUBTOTAL formula so that it will only count values greater than zero when data is filtered.
I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
I have a column, lets call it A, with a small list of airports. There will never be more than 7 airports in this list. There may be as few as one airport.
Now, I have column B which has ~100 airports listed.
All involved airports will be listed by their 4-letter code, i.e. PANC, PADG, PABE, and so on.
What I want to do is, if any airport in Col A is also in the list in Col B, to have that airport populate into a cell, like C1. Now, if two airports in A are also in B, then those airports should populate into C1 and C2, and so on for 3 or more airports.
I could probably do this by nesting 7 IF formulas to check each line one by one but, there's got to be a more elegant solution that can handle this?
If VBA is needed, that's fine too as this project already has macros in it.
I need to complete my business price list. Basically it's an XL sheet with all my suppliers products listed, there price, my margins, and my retail price and bulk price. Currently I'm fiddling with my margins and the best way to implement a formula to reflect these margins. Rather than do it manually, is there anyway I can create a forumla for different gross margins to be set for a certain range of the suppliers goods. For example, any product I buy thats costs me between $0-$20 has a set margin of 1.44 (44%), or a product who's price ranges from $100-$200 has a set margin of 1.26 (26%) etc. This would cut out then need for me to manually check suppliers prices and change to margin accordingly, thus saving me much time.
View 7 Replies View RelatedI'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsx
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI need to be able to query a large date range by a small beginning and end date range and return a count when the value is = each search criteria. i.e. - Search one year of dates from a table by Beg: 7/23/2012 to End: 10/21/2012 and return a count. The beginning and end dates are dynamic and I will need to reference the cells, i.e. B102 "Beg" B102 "End" and not a static date.
View 7 Replies View Relatedhow to subtotal data in filtered data. Here's my issue, I have to filter a huge range of data based on account type, then within that account type, I need to subtotal data for a select number of departments where the departments I need to subtotal all start with the letter d. I can't figure out how to create this type of subtotal on filtered data.
View 9 Replies View RelatedI want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
I am trying to sum a filtered list and when a filter changes I want the sum to change and it isn't right now. I have attached a TEST workbook in which all the data is values but in my situation all of the data is actually read from a workbook from each agent.... not sure if that matters.
If I filter by District (East or West), how would I get the total to change? I am trying to avoid any vba or macro within this workbook.
I'd like to have a dynamic report that ranks my lists based on an autofiltered list. In other words, I'd like to toggle the criteria that qualifies the lists content, and have excel generate "ranks" based on the values returned. Currently the rankings are based on the overall list, includeing the records that are filtered out.
View 4 Replies View RelatedI am trying to write a macro that will let me loop through an auto-filtered list (column C in example file) . I want to filter for a criteria (in this case a network), copy the filtered data, paste it onto a new workbook and save it. Although I have been able to record a macro (see below) that can do this, I have to copy and paste this macro over and over again for the other networks. I would like to know if there is a macro that can loop through each criteria in an auto-filter list, copy and save the data into new workbooks for me. I've attached a sample workbook that should be helpful in understanding what I'm trying to do.
View 11 Replies View RelatedI have a list 10 of names in Column A. ( On sheet 1 )
I then filter column A to show only three specific names, or four names etc. . .
On Sheet 2, in cell A1, I would like to list the three filtered names from column A on sheet1
e.g. if the three names showing in column A ( Sheet 1) are: john, mary, jane . . . then on sheet 2 cell A1, I would like to see: "john, mary, jane"
I need to apply a dynamic filter to my constantly changing list.
Here's an extract of the fields in Sheet "Materials". I need to filter by an of the Type Levels or by the "Index #" fields. The result would be used in another sheet. The return value should be a filtered list from the "Full Material Name" field.
Material IDIndex #Type Level 1Type Level 2Type Level 3Type Level 4Material NameFull Material NameM0000110010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized10gSheet & Plate - Galvanized - 10gM0000210010Class 0 - Raw MaterialsSection 0 - Metallic MaterialSheet & PlateGalvanized12gSheet & Plate - Galvanized - 12g
I'm trying to write a macro that goes through every value in a filtered list.
Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...
I need to perform a lookup on the first row of a filtered list. i.e. If I search for VCP/3301/MP the filter displays all rows containing that part number. The first row after the header row may be say A320. The next time I search for a different code the first row may be at A120. How can I select this first row for my lookup?
View 7 Replies View RelatedI have a macro I'm working on and am requesting your help.
In my filtered data, I have an empty column. For each cell in that column, I'd like to insert text, based on the results of the filter criteria.
I am using VBA only, modifying a huge .txt file into a .xls file. The ranges are dynamic.
I am successful in my results, aside from this issue.
I would like to paste values from a list into a different list - different tab - that is already filtered. When I try to do so, the values will paste into the hidden cells - being filtered -.
Find attached an easy example of my problem. The list I am talking about is around 1,000 lines.
Pasting Example.xlsx
To explain my example, I filter sheet 1 so only item I need are showing - B, D and F in this case - and I would like to paste the value of Alarm 3 from the Sheet 2 to the corresponding line in sheet 1. But it does not really work - line F is not filled - and when I un-filter values are filled into Item C and D.
I would like to know how to create a validation based on a list, but with filtered. I have an employee sheet and another sheet to select the names but only according to a specific job.
View 7 Replies View Related