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?
View 9 RepliesI have a two massive spread sheets in which I am comparing items from one with the other and changing in one if the other doesn't match the first. I already have reduced the processing time from 6 hours to about 2 minutes by rewriting the macro to use autofilter to make the changes. The problem I have now is that I need to keep some metrics on the changes i.e. when I apply an autofilter, I need to be able to assign a variable to the number of items actually shown after autofiltering so I can add it to a variable that is keeping track of total number of changes. I know that outside of VBA, in regular excel use, the status bar displays # of total# found during autofilter, so is there a way to tap into the program and pull that number during execution of a macro?
View 9 Replies View RelatedCounting unique items in column (numbers)
View 9 Replies View RelatedCan someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).
Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)
I need a formula to count the total items per unique id. For example
Column A Column B
11111 Basketball
11111 Basketball
11111 Basketball
33333 Baseball
33333 Baseball
22222 Hockey
Output to Column C,
ID 11111=3 Basketball
ID 33333 2 Baseball
ID 22222 1 Hockey
I've an excel sheet with 1000 rows data. I need a formula to count number of unique items in a column with a condition. Please find a sample data sheet.
I've tracking list that has ordered and delivered items for a given month on weekliy basis such as Week1(W1), Week2(W2), etc. I need a formula to count number of unique item delivered on this month.
Item 1 to item 8 delivered and item 10 also delivered in week 4, So totally I need to get the result as 9.
I was trying to use "countif" for validating and counting each row, it's throwing value! Error =1/(COUNTIFS(A:A,A2, c2:c23, ""&0)) i.e., = 1/countifs(unique items from column A, then nonblank cells in column C) but it's giving Value error..
I just need to count the unique item with another condition.
Item details
Order week
Delivered Week
Item 1
W1
W1
Item 2
W1
W1
[code]....
Looking for a formula that will give me a count of unique entries (i.e. duplicate entries to not be counted.)
basically a formula that says look in D4:D4001 and count only one of each entry....do not count the duplicate entries.
As the subjects states I need to count the unique entries in a filtered range.
View 7 Replies View RelatedA B C D
1 Yes Red Green
2 Yes Blue Black
1 No Blue Black
2 No Blue Green
2 Yes Red Black
I am interested in finding the number of unique entries under column A where column B = Yes; Column C = Red OR Column D = Black (answer is 2)
I need to count the unique for a customer. The areas to be counted are separated by blank lines. At present, I am using sumproduct(1/countif(range1,range1), to count the unique items. This formula works perfectly except that it takes me almost an hour to do this for all the measurements. Is there a macro that can provide me with the same results -- putting the same values where I am presently have the formulas (the cells that before the blank lines)?
View 6 Replies View RelatedMy company has a catalog of ~6000 unique SKUs that we sell online. Currently we do not have a way to quickly determine how much a given item has sold month over month, and the rate of that change.
I have an excel workbook full of our last year's worth of sales data. I have organized it by placing each month of data in its own worksheet. I would like to be able to create a search box that will allow us to enter in a product ID, and have excel then create a graph with each month's of sales for that particular item. Is that clear?
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies View RelatedI need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
I have data that is formatted in an Excel 2010 table. The two columns in question are [Invoice #] and [Description]. In the description column I have descriptions of products as well as freight. The same invoice number would be tied to the product description as well as its associated freight. I need to filter out certain product types and their associated freight items.
The macro I wrote creates an array of invoice numbers that I want to filter out and leave the remaining invoices, but I can't get the filtering part to work. Here is my code:
Dim Invoices() As Variant 'array of invoice numbers
Dim Descriptions() As Variant 'array of Descriptions
Dim InvoiceFilter() As Variant 'array of invoice numbers to filter
Dim i As Integer 'counter
Dim j As Integer
j = 1
[Code]...
What happens now is that it filters out all values in the Invoice column instead of only the values in the InvoiceFilter array.
When I use the advanced filter like this:
Dim oRange As Range
Set oRange = ActiveSheet.Range("A:A")
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.
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 ?
View 14 Replies View RelatedI have a code which works perfectly and adds the dates from Column A to UserForm1 ComboBox2.
In column B, I have textdata - UserForm1 ComboBox5.
I'd like to transform the code to add only the dates which correspond (cascading) to 1.1 textdata in ComboBox5 (Column B) and 1.2 dates in ComboBox2 (Column A).
Code: ...
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.
View 7 Replies View RelatedI 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)
How to make it copy over A, B, and C not just A and B? I tried adjusting some of the ranges, but I get a Run time Error 104. Also, what is the (Rows.Count, 3) doing?
Sub CopyUnique()
Dim LR&, LR2&
Application.ScreenUpdating = False
With Sheets("AY")
.Range("A1:B1").EntireColumn.Insert
LR& = .Cells(Rows.Count, 3).End(xlUp).Row
.Range("C1:D" & LR&).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A1:B" & LR&), Unique:=True
LR2& = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & LR2&).Copy Sheets("ORG").Range("A1")
Sheets("ORG").Columns("B:B").Columns.AutoFit
.Columns("A:B").Delete Shift:=xlToLeft
Application.CutCopyMode = False
End With
Application.ScreenUpdating = True
End Sub
I have a list of >50 contract numbers listed in cells A1:A350, with several of the numbers listed more than once. What I would like to do is, on a separate worksheet, list each contract number only once, in column A.
View 9 Replies View RelatedI have a Listbox1 in which I was able to populate it with unique items (from column B in Programs worksheet) using a collection (that was a good achievement for me!)
Now, I want to populate a the Listbox2 with items (not repeated) from column C, BUT only if the correspondent value on column B is equal to listbox1.value.
[Code] .....
I've been using the following formula from the xldynamic website detailing
sumproduct usage as a base for retrieving uniqe instances in a data series.
I've been trying to modify the formula to allow me to utilize one additional
criteria.
Original formula:
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
Here is my formula:
=SUMPRODUCT((($O$7:$O$2710<>"")*($M$7:$M$2710="1986"))/COUNTIF($O$7:$O$2710,$O$7:$O$2710&""))
I'm trying to say how many unique instances are there for column O, where
column m = "1986?"
I have a sheet of un-ordered data with duplicates. Looks a little like this:
Sheet2
*
A
B
[Code]....
What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:
Sheet3
*
A
B
C
[Code]....
I have a combo box (from the Control Toolbox toolbar). I like this to be populated with the contents of column A. however, in column A there can be duplicates. I obviously don't wan't any duplicates in the combobox.
View 9 Replies View RelatedI want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.
View 9 Replies View RelatedI have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:
Smith, John Brand A Beer 12 oz Beer Industries, Inc.
Smith, John Brand A Beer 8 oz Beer Industries, Inc.
Smith, John Brand B Vodka 700ml Vodka Industries
Jacobs, Sally Brand C Wine 1989 Wine Makers Co
Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc.
Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd.
Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.
So the totals would be:
Smith, John Total # of Manufacturers: 2
Jacobs, Sally Total # of Manufacturers: 3
So the formula ignores the duplication in employee names and manufacturer names.
Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))
I have a database of data that looks like this
Centre Name Training Type Expert 1 Expert 2 Expert 3 Expert 4
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre MyTraining Me
MyCentre OtherTraining Me
NewCentre NewTraining NewPerson
I am trying to create a user form (in VBA) with a combo box that has each centre name appear only once (despite the fact that in the database each occurs multiple times). When a specific centre is selected, this brings up all the different training types associated with that centre in a separate list box. When that training type is selected, it should bring up all of the experts in that training type (for the specific centre). I should mention that the range is static (though the data is always contiguous) as the database is updated on a going forward basis.
I have a column that looks something like (it is VERY long and has over 20 different names):
abe
abe
abe
abe
ben
ben
ben
cat
cat
john
john
john
john
tex
tex
I want to create another column (automatically) that lists each of of those once:
abe
ben
cat
john
tex
Any idea of how I can do this automatically?