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 am trying to create a search where the user types into the text box 'ItemDescription' then hits the 'ItemDescSearch' button (see below code) to pollute the list box 'lbSamDesc' with any partial matches from the specified range. Currently when I click on the button it takes about 8 seconds then no results are displayed in the List Box.
I've created a sheet and associated VBA codes and userforms for call logging in my department at work, which works beautifully at the moment, but I could do with some help with the next step.
I've a listbox at the bottom of the main userform which lists all calls received by my department, but I'd like to be able to filter this for either open, held or closed calls but can't seem to get this to go.
I was hoping that I could simply filter the worksheet it came from and it would affect the listbox, but this doesn't seem to work. The only examples I've managed to find of anything similar are a little too complex for me to work out what does each bit.
I am creating a database using excel and I've created a userfrom for ease of data entry and searching the database. But now I am stuck at the searching part.
The sheet is Sheet1 and the userform is UserForm1. Based on the pic that i have attached, user needs to key in the keywords in any of the textboxes and comboboxes in the group box labelled "Organizational detail" and when he/she clicks on the Search button, the userform will display the whole rows where the search results reside onthe listbox. When the user click on the search results on the listbox, the textboxes and comboboxes will be updated with the data on the listbox.
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.
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 list details in ranges "CV:DC" column CV has date entries so with the code given above i just select the date from combo box and check the details by pressing command button..Actually this code is doesn't work with combo box but it works with textbox except date data entries in this mode. So how can i improve this option if im going to use it in combo box.
Private Sub CommandButton26_Click() Dim a, i As Long, ii As Long, b(), n As Long ListBox4.Clear With ComboBox6 If .Text = "" Then Exit Sub If WorksheetFunction.CountIf(Range("cv:cv"), .Text) = 0 Then MsgBox "Bad Data" Exit Sub End If a = Range("cv1", Range("c" & Rows.Count).End(xlUp)).Resize(, 8).Value For i = 1 To UBound(a, 1)........................
I currently have this code that uses a textbox to search through the populated listbox and removes any entries not matching the value in the textbox. It works great, but if the backspace key is struck it cannot reload the listbox and narrow down the results again.
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.
aboute searching big database (10000 rows) with names of streets for one town. Each day I get list with 50 address and I need to look in database to see details aboute street in wich is address. I was thinking of user form with listbox for list of 50 addresses and then use first four letters of that address for advanced filter. I'm stuck with code for that four letter thing. When I see the desired information I need to insert it to 50 address table.
I'm trying to use 2 combobox's to filter a listbox with 7 columns. I need to match the 1st 3 chars. and the last 2 chars of the 1st column field to filter the list that displays in a listbox on a form. Is there an easy way to do this? I tried working with the autofilter, but couldn't get rid of the dropdown selection arrows.
I'm running reports in one workbook on different types of spend for 12 business units using 1 worksheet containing the raw data, and 6 other sheets with pivot tables showing different levels of detail.
I've used simple VBA to prepare to standardise the raw data and refresh all the pivot tables with no problem
I need to filter the data to show spend for specific business units. I'd like to do this using a listbox showing the units where I can select the unit(s) to report on, which in turn will only make the units selected visible on each table in the workbookwith no other intervention.
There is another thread on here (showthread.php?t=55041) that shows how to use the list box to control 1 pivot table on one sheet but I can't find anything which shows me how extend this to cover multiple tables over multiple sheets.
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