oRange.AdvancedFilter xlFilterCopy, , Worksheets("Sheet2").Range("B1"), True
Under certain conditions, I get one duplicated value on "Sheet2". As near as I can tell so far it is only when the first cell in my "oRange" variable has a duplicate(s) of itself elsewhere in the list. The said value then appears twice in the list (only twice out of 19 instances in my particular test).
Also, I noticed that it is automatically naming the first cell in my destination range "Extract". If I change "B1" (above) to something else and run it again it throws an error because of the duplicated name "Extract".
What am I missing? What is the purpose of the automatic naming?
It looks like I need more to guarantee that my filtered list is always 100% unique.
I have a Excel sheet and I have put Autofilter for a particular category I choose I need to count number of items in another column(autofilter) , for eg... there are two columns Category , Items When i choose a particular Category I need to count number of Items in the autofilter for that particular category?
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
I have a table and say I would like to create a unique list from column2 and store the list in a spearate sheet. Give this unique list a name.
Use this unique list in a FOR EACH loop and apply autofilter to two tables on two different sheets. (one table is from which the unique list was created and other in a different sheet)
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple 0001: Apple 0002: Google 0003: Sony 0003: Sony 0003: Sony 0003: Sony 0004: Ford 0004: Ford 0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
Each product is represented by a serial number (column A). The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
I have a list of item numbers in a column which is Autofiltered. I can go to the autofilter drop down and then to custom and select two item numbers to allow through the filter, however I have about ten item numbers that I want to allow through the filter.
Is it possible to have multiple instances of autofilter on a single worksheet? The two autofilters should not be related to each other and are on different sets of data (in different rows as well as columns but in the same worksheet).
I have a situation - where I have a table and a "eSubtotal" cell that basically shows the subtotal value when Autofilter is ON and a SUMIFS calculated value when Autofilter is OFF. I have written this in the Selection change event of the sheet.
For this purpose, I have perform a regular check of AutoFilterMode = true or false and based on this result, I change the formula in the cell, eSubtotal.
Now the challenge is, I don't want to apply SUBTOTAL formula when AutoFilter is ON but there is no filter in any of the columns. I want to keep the SUMIFS just like that in this case.
i am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.
Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?
I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.
I have a workbook of approx. 60,000 rows, with about 20 columns including a source identity column, such as 'Leeds' , 'Barnet' etc..
What i need is a solution that will auto filter all rows that have a value of 'Leeds' in the source column into a new workbook called 'leeds.xls' for eg. and so on (for each unique source value) and loop until the whole data set has been filtered.
Saves manually filtering, copying and pasting....over and over.....
Im guessing the VBA needs to build / look at an array etc...
I have an spead sheet with 9 sheets 1 master and 8 sub sheets. The master sheet runs for col A to AV and the it can have x amount of rows. At the moment i use the following but this takes an age.
The attached file has two sheets, “order” sheet and “stock” sheet. Can some help me with the followings:
1-In either sheet, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, the VBA performs an Auofilter of the cell I have selected.
2-In sheet “Orders”, I would like a VBA where if I put the mouse on a cell and then click the VBA button it takes me to sheet “stock” and performs a find function of the cell I choose in sheet “orders”. For examples, if I put the mouse on cell E21 in sheet “orders” and press the VBA button, it takes me to cell H297 in sheet “stock”.
3-In sheet “stock”, I would like a VBA where if I put the mouse on a cell and then click on a VBA button, it takes me to sheet “orders” and performs an Auofilter of the cell I choose in sheet “stock”
