Populate Adv Filter Criteria from Inputbox with valid data
I've set up a macro to extract 3 different sets of data using Advanced Filter (same data range, different criteria & extract ranges)
There are multiple options (different individual dates) to satisfy the criteria (a date) for each of the 3 extracts and I want the user to select the criteria (a specific date) from the range of unique values in the data range upon which the Advanced Filter criteria is going to be applied. (A bit like the effect provided by Autofilter)
My initial attempt was to capture the criteria date from the user and populate a cell, and then have a vlookup function in another cell check if that crieteria value exists in the specified range. I then ran a loop in the VBA code until the value is valid (ie. exists in the range). However, to streamline this I was hoping to allow the user to see a list of the valid (available) dates when they respond to my Inputbox request for the criteria.
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData() Dim Lrow As Long, test As New Collection Dim Value As Variant, temp() As Variant ReDim temp(0) On Error Resume Next With Worksheets("Sheet1")
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue() With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value) End With End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("$A:$A")) Is Nothing Then Call FilterUniqueData End If End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant Dim row As Range Dim elements() As String
1. Using a formula, I am trying to to obtain a list of unique values (string) (caveat: see #2) from the range E2:E10000 (arbitrarily chose 10000 - the row number is variable)(see #3).
I currently have a formula that seems to work for this purpose but I don't know how to add the condition in #2 (below)
2. To include all unique string values except those starting with the letters "IC"
3. Is there a way to make this formula so that it can only seek values up to the last row, and not go to the 10000th row if not necessary? The E column has no empty cells until after the last row that contains data.
Here is the formula I currently use which serves #1 (above):
Any way to improve/simplfy this formula for the purpose describbed in #1? How can I add the condition in #2? Can you see a way to include #3? The most important issue here is #2.
Example of desired results:
Column A | Column B AA | AA DD | CC AA | DD CC | DD | DD | IC | IC |
I have systems that spit out text log files showing machine performance. Each log file has about 5000 lines of data in it which I import into excel for processing. One of the columns has a machine voltage that changes with the machine state. There might be 10-15 discrete values (like 500V, 800V, or 1000V) within the 5000 lines. I'm trying to populate a list box with just the discrete values so the user can choose what state to process for charting. Some log files might only have 2 or 3 voltages others might have 15 or more so it isnt fixed. I've been trying to use an advanced filter with copy to location and unique records selected but I run into problems with the variable size of the list and with the fact that the header repeats periodically and I can't seem to screen that out of the list.
I have values in COlumn A, probably about 50K rows, The total unique values on the column A is only about 27. I need to capture all those unique values and paste them in column B. I didn't want to do "Advance filtering" since the workbook has many macros's on it that the user simply click on. Im thingking of creating a loop and compare values already pasted in Column b but then that will take too much time to complete the loop. Is there any easier or yet more efficient way to accomplish this?.
I have a list of all transactions that happened during the day at different locations On another sheet I want to list all the locations and in another column to populate a list of unique transaction codes for each location from the first sheet (all unique values for each location in one cell). I am attaching a spreadsheet in case I am not clear enough (I need to create a Summary). This attachment is really a simplification of a file I work with. The real one has many columns and multiple subtotals, I would not be able to incorporate a method that involve filters
- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!$B$2:$B$100 this will relate to a completely separate book on a company network.
Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?
I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?
I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).
(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)
Event Manager Status 1call A yellow 2sleep B yellow 3run C red 4jog D pink 5eat E pink 6call E yellow 7sleep F red
I need to output 4 unique Status before the event sleep. When I say unique it is compared to its previous status. So that would be [yellow,pink,red,yellow]. Notice that I could repeat yellow but not a sequential yellow. I cant use previous formula on listing unique values because I have reference point which is sleep, that i need to lookup from a huge pile of data, and i need a counter to move up. I tried vlookup with countif but i still have to define the max range. The data is huge and my menial skills are limited to excel.
I need to create time sheets for about 30 contract workers.
Each time sheet needs to have their name as well the date for sunday of that week inserted into the form. (once the date for sunday is entered into the sheet, the rest of the dates for the week will be extrapolated out).
Then, once the data is inserted for a single worker, I need that file to be saved using the workers name in the file name.
The script would then open a new time sheet template, insert the second workers name and the date for sunday and save the file. Repeate for next 30 workers.
See attached file with a smaller sample of the 1667 row table of data I am working with. What I am looking to do is make a list on another sheet of each every line with a county and have the corresponding Township and range with it. So if I have a cell and I select Audubon county, I would like a list to populate that has the 12 lines of Audubon county in column A, and the Township in column B and the Range in column C.
I am trying to create a macro that will create a unique list of the values in the same column across 5 worksheets, "Question1", "Question2", etc, and place the list in a new worksheet, "Summary".
The values are in column A in all of the worksheets, and the number of rows in each worksheet will change each day. I need the unique list to be added to the sixth worksheet, "Summary".
Also, after the list is created on the Summary worksheet, I need a count of each field by worksheet and a total. This is how I currently have the Summary set up:
Part Type|Total|Question1|Question2|Question3|Question4|Question5 Name|=SUM(C2:F2)|=COUNTIF(Question1!$A:$A,A2)| =COUNTIF(Question2!$A:$A,A2)|=COUNTIF(Question3!$A:$A,A2)| =COUNTIF(Question4!$A:$A,A2)|=COUNTIF(Question5!$A:$A,A2)
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.
Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.
I have a list of sales events that ran at various venues, together with how many people attended and how much revenue was brought in by sales. This data is arranged by week. One venue will never have more than one event per week, but different venues may have sales events on the same week.
My table looks like:
In reality this list could be thousands of rows long.
“Event week” is the calendar week of the event. “Weeks open” shows for each event how many weeks that venue has been open (may or may not be useful!).
What I need to do is to be able to pick a number of weeks (preferably changeable by entering into a cell) and have a list produced of each venue that has run for at least that many weeks, and the sum of people and sales in those first X weeks. My idea of output, if the user input “2” as the number of weeks would therefore look something like:
Note that 1)only venues that have run at least 2 meetings are shown in the output 2)even if venue has run over 2 meetings, only people and sales figures from the first 2 are summed up and displayed.
In this case. Later on I need to produce a frequency chart/histogram grouping the results into categories, but I know how to do this as long as I can get to the step before – but wanted to mention it in case there are shortcuts.
Have been battling with this with pivots, database functions, array formulae etc. for a long time.
Attached is the spreadsheet with sample data. The red table is the part I am stuck on. The solution has to work in Excel 2003.
I'm trying to count the number of unique values from a list of over 8,000 records -- and it's very likely that nearly all of the records are unique. At the moment, I am keeping track of an array where I store every unique value I find, expanding the array and adding values as I go, and at the end return the size of the array to tell me how many unique values there are in the range. My method works, but it is very slow, even when I turn on manual calculation.
Here is what I have so far:
Function CountUniqueCases(inputRange As String) Dim i, j As Integer
Dim cCaseID As Range Set cCaseID = Worksheets("Results").Range(inputRange)
Dim uniqueCases() As String Redim uniqueCases(1) uniqueCases(1) = cCaseID.Cells(1).Value
Is there a faster way to do this? I was hoping there was an array search function built in to VBA, but apparently not. My first attempt at running this function returns about 7904 out of ~8000 as being unique.
I need a UDF to make live easier. I have a huge excel file and I need a final function (I suppose a UDF) that lists all values from a range spanning over multiple columns and rows. I would not really like a macro, since it does not update when certain values are changed.
It's part of a bigger functionality, but I need this one, really bad, since it spans over 6000 rows as the search range, and 7000 as the search values. In short: I got to list all the unique values. They are in specific cells on worksheet 1 (A1:C5 and A25:C31) and should be listed on worksheet2 starting in field A2. As 'simple' as that. Nothing more, nothing less, just list all unique values of a certain range, which I specify. If it can not be done, by selecting the two fields separately, it's no problem at all to list all values in eg A1:C6000. Do note that some of the cells will be blank, and I don't need blanks. I only need the actual data sets. I added a sample excel file, that shows a representation of the data. I can not upload the actual file, since it's way too big (60 MB). So a sample file should give you the idea.
Three sheets: Data 1: some value lists multiple columns Data 2: some value lists, again multiple columns Desired result: the result as it should be listed. example.xls. zip
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A ABCD ABCD ABCE BDCE BDCF ZXYE ZXYE YEZY CDEF
Col B is where i would like to list all the unquie vaules with a count e.g. ABCD 2 ABCE 1 BDCE 1 BDCF 1 ZXYE 2 YEZY 1 CDEF 1
I need to be able to pull information from 5 columns on 5 different pages, and pull all of the unique names out of it. From there, I need to create a list of all of these names. More often than not, this could only be three names, but I still would like this to be automatic because eventually it could be 50 or 60, and manually editing this list would get tedious. Lastly, is it possible to have a list create cells? For example: I have information on row 4, this one will start on row 6. My next group of information starts on row 14. That leaves 7 rows for information. I don't want to leave a large gap at the top of the page, so I'd like to be able to create cells if the list I'm trying to create has more information on it. Seems plausible in my mind, but I'm still new to excel.