Retrieve Unique Items With 2 Criteria

Nov 29, 2005

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?"

View 10 Replies


ADVERTISEMENT

Count Unique Items With Multiple Criteria

Aug 12, 2009

A 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)

View 9 Replies View Related

Retrieve Selected Items In Listbox

Dec 5, 2007

I have a listbox with several items selected. Is there a way to automatically show the selected items in the worksheet (as values in cells)?

View 8 Replies View Related

Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0

Jul 17, 2009

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.

View 9 Replies View Related

Retrieve Last X Entries By Criteria

Jul 27, 2007

I have a spreadsheet with columns A:AH. I need to be able to retrieve the last 50 entries based on a number from column M (Area). For example, I need a message box asking me what Area I want. (The choices are 1 to 7). When I select the Area number I need to retrieve columns G:J for the last 50 entries for that Area. In selecting the entries, it needs to ignore any rows where there is either a blank cell or a period in column I. This is a dynamis spreadsheet with rows added almost daily.
Zipped spreadsheet attached.

View 2 Replies View Related

Excel 2013 :: Retrieve AutoFilter Criteria VBA

Jul 20, 2014

Is it possible to retrieve an arrary of the autofilter criteria. I know this is possible in pre 2007 but with 2007 onwards I can't find a way of doing it.

I know I can get all the available items in a list by using the scripting.dictionary and also by using the visible cells I could see what could be filtered but that is not really accurate for what I want.

E.G.

If I have multiple columns and look at the filtered information in one column I can retrieve an array of the visible cells from that column but that is not necessarily the criteria that is in the column. i.E. If another column has a filter rows may be filtered that would have otherwise been visible.

View 5 Replies View Related

Retrieve Data From Multiple Worksheets By Criteria

May 24, 2008

I have multiple worksheets to add employee details using a form and create worksheets (copy templateSheet and paste) for each employee using their uniq user id, which works fine. I have 12 worksheets named by month (Jan, Feb, Mar.... Dec). I have only 2 worksheets Jan and Feb for test.

I want data from employee worksheet's (worksheets created by employee user id) to be in 'Jan, Feb... Dec' worksheets to use a criteria (userid, worksheet name) to get monthly holiday booked data from employee worksheet (userid and worksheets name are same) to month name worksheet. I can do it manually (Example: =rahmanm!C3

) for each row and column, but I will also have a function to delete employee name and details. Is there anyway in VBA I can update worksheets (Jan, Feb, Mar... Dec) from employee worksheets (rahmanm, hallD, aldridh.. ).

View 3 Replies View Related

Add Unique Items

May 7, 2007

I 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: ...

View 9 Replies View Related

Copy Unique Items

Jan 8, 2009

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

View 9 Replies View Related

List Unique Items ...

Jun 15, 2009

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 Related

Count Unique Items

Jun 16, 2009

Counting unique items in column (numbers)

View 9 Replies View Related

Count Condition Unique Items

Jan 22, 2009

Can 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.)

View 14 Replies View Related

Count Total Items Per Unique ID

Feb 19, 2014

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

View 11 Replies View Related

Listbox With Unique And Dependent Items - VBA

Sep 25, 2013

I 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] .....

View 4 Replies View Related

Dynamic List Of Unique Items?

Sep 13, 2012

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]....

View 7 Replies View Related

Count Unique Items With A Condition?

Oct 30, 2012

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]....

View 3 Replies View Related

Populate Combo Box With Unique Items

Dec 19, 2006

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 Related

Count Unique Items In A Column

Dec 7, 2007

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.

View 9 Replies View Related

Counting Unique Number Of Items

Sep 15, 2008

I 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 Related

Subtotal Non-Unique Items By Employee

Nov 28, 2008

I 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)))

View 9 Replies View Related

Add Unique Items To ComboBox Control

Aug 30, 2006

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.

View 2 Replies View Related

Count Unique Items In Autofilter

Jun 10, 2007

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 Replies View Related

Formula To Retrieve Data With Multiple Criteria And Dynamic With Dropdown List

Jan 6, 2014

I have a complicated situation with Excel since I need a formula that can SUM data from the Pull 1 worksheet into the summary Sheet. I attached my sample for your reference because it is a bit complicated therefore I can not explain. It is better if you look at my Summary Sheet, need formula that can retrieve the same information.

Formula Test.xlsx

View 3 Replies View Related

Formula To Create List Of Unique Items

Feb 12, 2009

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?

View 12 Replies View Related

Count Unique Items In Filtered Range

Dec 13, 2009

As the subjects states I need to count the unique entries in a filtered range.

View 7 Replies View Related

Counting Unique Items(values Or Text)

Aug 3, 2006

I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.

so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1

now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter

now what should i suffix or prefix to this formula to count unique values in column A.

View 11 Replies View Related

Counting Number Of Unique Items In Column

Oct 10, 2008

I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data

a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears

in this case number of uniques items is 3

Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.

View 6 Replies View Related

Counting Items Unique To A Day/customer Combination

Feb 3, 2010

I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.

To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.

View 3 Replies View Related

Populate Combobox List With ONLY Unique Items ()

Nov 16, 2006

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.

View 9 Replies View Related

Display Unique Items That Occur More Than X Times In Column?

Jan 25, 2012

I have a long column of 9-digit numbers which represent the swipe-card-ID's of patrons accessing a parking facility. Most of them occur more than once, some of them many times. There is an operational rule that bars them from using the facility more than x times. (I can't implement this limitation in the parking software/hardware, or I would happily do so.)

So my question is, is there a way to process through that column, identifying each unique ID, and reporting any that occur more than x times?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved