I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
I have a very large spreadsheet (almost 9000 rows). I have filters on all columns, however when I click to see all the results for the column under the filter, just to view the contents of the row, some are not there. Does a filter have a limitted amount of rows it can hold.
I have spreadsheet that tracks flights between cities. The spreadsheet has become quite large, so I need a few simple tools or tricks to search it efficiently.
To keep things simple: the spreadsheet has "Departs" and "Arrives" columns, tracking the cities on either end of a flight. (See below)
If my boss says, "give me a list of all flights going through New York," I have to manually filter for "New York" in the "Departs" column, copy that to another spreadsheet, then filter again for "New York" in the "Arrives" column, and manually glue the results together. In my little toy example, that's not a big deal, but when there are up to a couple hundred flights (and just as many cities), this gets tedious and error-prone.
Is there some handy way of filtering for "New York" in both the "Departs" and "Arrives" columns? Or some other way of achieving the same thing? ("Find All" isn't useful unless I can export the full rows into a spreadsheet.)
I have a basic spreadsheet with a column of names of people (Matt, John, Mike), and specific sales numbers for each. I would like to have a cell give an average of the sales numbers based on how I filter for either Matt, John, or Mike individually.
For example: If I filter to see only Matt, I would like to see the average for Matt only, and when I filter to another name such as John, I would like to see the average number change to see only John's.
I have a workbook with multiple sheets , final sheet has a data of all sheets , in the first sheet i want to select what results to be shown in that final sheet ,, specially the week and the LDM
I want to filter my results using a combo box on my spreadsheet. I don't mind how this is done, even if it just hides all of the information which isn't relevant. I've done it with a VLookup but I want to be able to edit it easily without the formula popping up. So if you click Barlcays, only barclays appears. I'd also like a button to reset if possible. I've attached an example of how I sort of want it to work.
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
A friend of mine asked if there is a formula that will add the "Y"'s and "N"'s based off of a filter per certain dates. Below is a brief example.
2/18/2009Y 8/15/2006N 2/18/2009Y 8/15/2006N 4/6/2005 N 4/6/2005 N
We want to be able to sort by dates so regardless of which date we pick, below the Y's and N's will be the total for the Y's and N's that are visible. If sorted by April, it will show 2 N's and 0 Y's, and so on. Is there any formula out there that can do this.
I'm trying to get certain data from an export of a phone logging report for a call center. I have exported this data into an excel spreadsheet. I would now like to retrieve the following information from the sheet.
'Agent name, Date, time of Login, time of Logout (at end of day).'
I have coloured these fields in red for easy finding. I do not require anything else from this data, however, within this sheet there are multiple agent names and can span for over a month. All names and dates are required. I have attached the current sheet, and also what it will possibly look like once complete. I would prefer the whole process to be automated if possible.
I have a worksheet of 10 Columns and over 5000 rows. The worksheet has the word {Group} in brackets after the file name. When I pasted this worksheet into an old file I have been using for a long time it added the word {Group} to this file also.
I am unable to use sort or filter on this worksheet as they are both grayed out.
I have two columns of numbers. The first column is numbers of customers seeking specific products. The second column is of total competitors for the products. I am wanting to find a sort or filter that will show me the "sweet spot" where I can "weight" the best products that have greatest seekers but lowest competition.
Here are a few examples of what I am working with:
Product a - People seeking product: 167 / Competitors providing product: 5740 Product b - People seeking product: 111 / Competitors providing product: 133000 Product c - People seeking product: 74 / Competitors providing product: 105000 Product d - People seeking product: 60 / Competitors providing product: 109000 Product e - People seeking product: 33 / Competitors providing product: 22200
(I have many more products than this, this is only a small example)
So what my goal is is to run through all my data and find the most ideal products (the "sweet spot") that have greatest number of seekers/potential customers with the least competition.
Is there a way to sort this? Or do I use a filter of some type. My knowledge of Excel is pretty minimal.
i have a work book of 12 sheets, the two main sheets 1+2 have 5000+ rows. by 30 columns. each row column b is a site number in ascending order(up to 4 for each site) so auto filter select = to site 5 about 10 seconds later i get the result of 3 sites try site 846 30 odd seconds later up pops the result. so i copied the sheet to a new work book and tried and it's nigh on instantaneous. next step copied sheet paste special values into new sheet in same original workbook and tried filter on that still slow as ever, thing is this book around 8meg and i cant for the life of me work out why ive cleared excessive formats and reset last cell on each sheet. there is a vlookup from sheet 1 to 2 for each site to return serial numbers. i have tried it in vba by turning autocal off/on but still no difference.
I created a user form that provides a menu that allows users to perform advanced filtering. They can also scroll through the results freely. A problem is that a subsequent advanced filter selection does not always display the top row of filtered records, depending upon what the user has chosen to display prior to selecting the next set of filtered records.
How can I make the top row of filtered records always be displayed so that users will not overlook results of advanced filter operations?
But what do I use to count-unique values of variable D ?
=SUM(IF(FREQUENCY(RangeD,RangeD)>0,1)) doesn't work. =SUM(1/COUNTIF(RangeD,RangeD&"")) doesn't work either
They don't work because D can occur during different combinations of A,B and C. In other words, the D value of 'Smith' can, and does, occur when A is 1,2 or 3 and additionally when B is Pass, Fail, Withdrawn.
They "don't work" because I can use filters on A, B, C and D to see what the correct answer should be .. and the function doesn't return the correct answer.
I hope I've explained this ok - I've been searching on several excel-help websites for 2 days now and one of the days my client is going to want some results.
I have the following code that copies only the visible cells in an auto filter...how could I modify this code to paste only the values and not the format?
now i have filtered data in a table, i want to use the results by printing off a table showing just these and the appropriate rows from other tables on worksheets. these all have a specific ID which is how they are traceable to each other. like a related field in an access database. two tables/worksheets are like so:
table1: Reg No | Rank | Name | Initials | Troop | etc
If i were to filter the second table by JCLM1 = YES then i want to create a printable list of all those but also to include the related records from table 1. is this possible?
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
For some reason I have a worksheet that won't sort stuff by Autofilter any more. I built it as a customer database but for some reason today I just can't get the names to ascend or descend. there is something simple I can check for, I can post an empety sheet if needed but right now I can't really post a list of all my customers info.
I'm tryin to set up a macro to filter and sort and when I record the macro correctly it does not run properly.
I have detailed info below:
I created a sheet "Met Logbook" which contains all links to the source "Master Logbook". This master logbook has a lot of info added daily and we add info at the top of the sheet (we insert new rows every time at the top for different reasons). So what happens is the master logbook always changes its cells (when inserting new rows at the top, all the cells change row number). So I have just linked all the cells and more that we may be using in the Met logbook. This is why I need to filter and then sort the Met logbook (since there will be so many blanks, unuseful info and in the wrong order). (It will be in the wrong order because the "master logbook" has newer info at the top and I have more than one sheet of info for the master logbook) (so my links in the met logbook are somewhat out of order).
So what I need is to have code to filter column B with: ã and column H with "Blanks", then I need to sort column A (rec'd) in descending order (so that newer info will be at the top). I will post my Met Logbook sheet on my second post since its a little large.
I am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.
I'm trying to write one macro that filters a set of data based on 2 criteria and then automatically sorts the filtered data by date. The filter works fine but when I then try to write code that handles the automatic sort, I get the following message when I run the macro: "Run-Time Error 1004: the sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."
I realize it would only take one or two more clicks to sort this data manully after the macro is run, but the lazy person in me is trying to eliminate this step. Does anybody have any suggestions as to why the sort is not working? I parsed basic sorting code, but it hasn't worked. The other part of this that is causing me trouble is that the number of filtered rows will vary, so I'm only needing to sort the visible range.
I have a file with a bunch of columns in it. Columns C and D have names in them. I want to filter on these two columns where the results show all rows where the selected name is in both.
Example, If I filter on Column C first on 2 specific names (Joe and Chris), it will not show the results in column D if that name was aligned with a different name in column C (Mark and Steve). I need all rows to come back.
Column C Column D Joe Joe Chris Chris Joe Chris Joe Mark Joe Steve Chris
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
I need to filter a large database using multiple OR criterias.
Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.
I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.
