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 a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
I'm creating a spreadsheet to track orders as part of a project, the spreadsheet currently lists all the different options that can be ordered, it then lists all parts of the order, installation & setup etc.
However it is likely that not all of the options will be used.
What I would like to do is create a few drop down boxes for each option, if 'yes' is selected then the order tracking for that option shows below.
Example, the wants 'internet' so 'yes' is selected for internet using the drop down at the top of the page. Further down the page all the order tracking info is shown for internet. If 'no' is selected nothing is shown for interent.
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 have a wordlist (65000 words in Column B*) in a worksheet “w1” and poems (about 21000 rows) in another worksheet “w2” where first verse is always in B and second one in C. The column D of w2 contains of information such poems name, author’s name, book’s name and so on.
I want to search for those verses through a macro which contain words from my wordlist B* w1 and add references to my words that way. It’s about making a dictionary in which each word has a reference to a poem and verses in which it is used.
Column A of w2 has number in it which shows how many times these rows have been already used as reference. Column A of w1 contains of a value “1” or “0” in which “1” means this entry has already been processed or already has a reference and “0” means it has yet to be done.
If my word B* from “w1” exists in verses B or C of “w2” the results should be shown in an userform containing 5 text boxes with 5 results. In each text box a result should be shown “(verse B; verse C); (value of Col.A)” and a checkbox (or a button). That would mean 5 result, 5 textboxes and 5 checkboxes (or 5 buttons). I would like then to choose one of the results by checking the checkbox (or pressing the button) in front of the text box. After I have chosen the result the whole row from w2 should be pasted in following columns of B*. That means the cells from w2 column B,C,D would be pasted to column C,D,E of w1.
The criteria for the search in B and C should be the lowest value in A of “w2”. I mean the w2 rows with lowest A value should be preferred if there are more results. The result showing user form with 5 text boxes and 5 checkboxes(or 5 buttons) which gives me the choice to choose one of the results should also contain a button “search for further results“. For the case none of the results is useful.
As far there are many poems (verses) I would like to prevent using the same verses many times. The more different verses I use as reference for the words in w1 the better it is. Therefore every time a result is picked by me “1” should be added to the value in Column A in w2. And every time search is started the lowest A values should be searched first.
As soon a word has successfully got it’s reference the value in A w1 should be changed from “0” to “1”. And by next search all words with A value “1” should be ignored and only “0” words should be searched for.
Each time a row from w2 is used as reference, the B* word w1 should be added to col. E of w2. If used for many ";" should be the seperator.
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 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.
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 would like to be able to enter the data into the grey cells and then have the yellow cells auto generate a result. The only problem is that the required value in cell B5 is from when the entered value in B4 is looked up in the table, rounded UP to the next highest value and then the answer from the next cell.
So, if I enter in B4 a value of 500, a lookup is made in the table and the next highest value would be 553 with the correct answer shown in B5 being 13.
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.
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
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
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.
I got the following code from Use AutoFilter to filter and copy the results to a existing worksheet and would like to incorporate this into my VBA project. The problem however is that this code were written to perform on one workbook and this is where my problem is. My project is between two different workbooks and cannot seem to get this code modified to do what it is supposed to do between these two workbooks. Everything I have tried so far failed. In short what this code would do is to check the existing data on the one sheet (the source) and extract only the data which is meeting my set criteria, and copy this data to the destination sheet. This is what I would like to do between two workbooks. With this the sample code as provided by Ron de Bruin. The sample workbook could be accessed trough the following link [URL]..... With this the code for matching and copying on one workbook.
Code: Option Explicit '>> 'This example will copy the filter results below the existing data on the destination sheet. 'Note the sheet "RecordsOfTheNetherlands" must exist in your workbook. 'This example will not copy the header row each time so when you manual add the worksheet '"RecordsOfTheNetherlands" to your workbook you must add the headers yourself on the first row.