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
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Creating A Macro To Count Unique Items In A List
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 Replies!
View Related
Selecting Unique Column Items Into Seperate Worksheet From Column
In the attached file, details sheet contains multiple instances of project with associated costs for each of 2006, 2007 and 2008. What I need is a formula (preferably) or a VBA that select distinct project names and populate column B of summary sheet so I can do a sum if. The problem is the project names changes dynamically every week and they are practically in hundreds.
View Replies!
View Related
Count Unique Items In A Column
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 Replies!
View Related
Counting Number Of Unique Items In Column
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 Replies!
View Related
List Of Items In A Column
I have a list of items in a column. I would like to have a userform pop up with a list box (?) of all the items and I want to be able to select multiple items to perform an action on the row corresponding to the selected item. I am thinking of other examples I have seen where there are two list boxes (?) and then arrow buttons between them. When you click the arrow the item moves from one list box to the other to see which have been selected. I have never used list boxes before so if you have a link to some sample code that would be useful.
View Replies!
View Related
Adding Items To Multi Column List Boxes
I am using a ComboBox to get a value from the user. The code below will then check my worksheet and if it finds the value will place it into my ListBox. Dim x As String myvalue = ComboBox1.Value A = 5 Do x = Sheet1.Cells(A, 4).Value If x = "" Then Exit Sub If x = myvalue Then Me.ListBox1.AddItem x A = A + 1 Loop Until Sheet1.Cells(A, 4).Value = "" The problem is how to alter this code to place the value from column 5 into the ListBox as well as the Value from column 4
View Replies!
View Related
List Unique Items ...
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 Replies!
View Related
Creating A Clean List From Select Items.
I have 2 sheets, 1 sheet has a list of materials that require Certifications. The 2nd sheet is actually a report of which materials is needed for the specific job at hand. Column C will be linked to check boxes on a different sheet in the future (thats why i used True/false instead of Yes/no). What i would like to happen is when a cell in Column C is TRUE the information in column B be sent to the Certifications sheet in a list from C8 to C34 with no spaces.
View Replies!
View Related
Populate Combobox List With ONLY Unique Items ()
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 Replies!
View Related
Formula To Create List Of Unique Items
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 Replies!
View Related
Parsing A Row Of Multiple Items From A List Of Matching Unique
I have two worksheets: A and B. Worksheet A:Contains 2 columns: Issue# (Col. A) and Program (Col. B). Issue# contains a list of multiple issues. There are several instances in which the issue# is repeated. Any particular Issue# field may have several issues in it, delimited by a comma. Program is a program associated with the issue and this column also contains duplicates. Worksheet B:Contains 1 column: Issue# (Col. A) This is a unique list of issues#'s. All Im looking to do is parse all Issue#'s from Worksheet B and have some way of knowing if that issue# is anywhere in Worksheet A. Most importantly, I need the "indicator" to be displayed on Worksheet A. This way I can see what program(s) is/are associated with the matching issues. A couple other notes:All Issue#'s in Worksheet B are referenced at least once in the Worksheet A Issue# list There are several issues in Worksheet A which are not referenced in the issue list on worksheet B (of which I dont care about) I really hope that makes sense, but if not... Here's the best example I could come up with: ....
View Replies!
View Related
Divide A Sum By Number If Items In List
I basically have lots of lists of 12 cells. how ever some of those lists contain errors (e.g instead of having 12 numbers it will only have 10) so instead of doing =sum(a1:a12)/12 i want to divide by the amount of numbers in that area in this case 10 so i can get an average.
View Replies!
View Related
Excluding Specific Items When Dragging Down
Im using the following formula to update our prices in our stock list. =VLOOKUP(C1,'Most Recent Prices 2009'!A:G,6,0) Column C = Unique part number. Problem: I have a tab of 100 or so prices that I don't want to be updated. How can I exclude these pieces of equipment when I drag down the above formula. Assume that these pieces of equipment are in a tab called 'keep' and their unique part numbers are in Column A.
View Replies!
View Related
Replace Column Items
I have a form that will open a workbook and then run a function to replace items in a column based on the column name. The problem is that it only replaces some of the items. Here is my click event that calls the function
View Replies!
View Related
Count Different Items Within One Column
1. I need to count how many different items within one column, these are formatted as Text. cells G63:G1600 Sample text: F41D6 F44DD FABHD FAXHD FDTGD FFDGD FFTED FGDFD................... 2. I need to count how many dates fall during the week using the ending dates. Weeks ending 1/06/07, 01/13/07,etc L2:L53, the dates to count are L63:1600. Sample Text: 09/05/07 09/21/07 09/07/07 09/17/07 09/21/07 09/05/07 10/03/07 09/13/07 09/12/07 09/18/07 09/18/07.............
View Replies!
View Related
Count Of Repetitive Items In Associated Column
There is a list of interconnections of panels. For example: From To .................. P1.........P4 P2.........P4 P1.........P5 P1.........P4 P3.........P5 P2.........P4 P2.........P4 P2.........P3 There are hundreds of sheets like this with more complex panel numbers (there are 70 to 80 panels). I want to count the number of times each panel is linked with another panel i.e the number of times its name is mentioned in front of it. This may look like as follows. ............P3.........P4.........P5 P1..........0..........2...........1 P2.........1...........3...........0 P3..........0...........0...........1
View Replies!
View Related
Pulling Items From A Multiple Column Range
I need to be able to pull the information into another section of the workbook and place it into order (ie Black 1,2,3,4) and then drop down to the next row and place Blue 1,2,3,4 etc. I am using a drop down menu to select the Course, and want the times to then populate to the right, each in its own cell. Have tried the loop code available on a few different topics here however has not quite worked.
View Replies!
View Related
Pivot Tables. Too Many Row Or Column Items
I have made a report in pivot table. My Headings Sales A Sales B Sales C Region Name Location Code Location Code Category Location Cluster Head After made the pivot i started the grouping but is ther any limitation for excel for this? It is showing the below message. "Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable, or to page position. Alternatively, right click a field, and then click Hide or Hide Levels on the shortcut. How to over come this and do the grouping. Is this limitation of Excel. I am using Office 2000
View Replies!
View Related
Removing And Replacing Items In Multi Column Listbox.
Ive been having lots of trouble programing a multi column listbox to play ball. On the form there is 1 tree view and two listbox's. The object is to double click a list 1 item and have that go into the list 2. Connected to the listbox2_dblClick.. Takes the selected list item and adds to columns 1 intergra value. This places a annoyance to me where listbox 2 is not fully populated (you will see ive fully populated the list during my own testing) and a item is selcted if double click on a blank part of the listbox it will increase the int value of the selected item. I though of populating the list with blanks (used "." as visual) so a cheek could be made on the double click item and if blank do nothing.....
View Replies!
View Related
Sumif With Multiple Items
I am using a sumif formula where if column b is a certain number then it will add up the value in column e. This works fine, however now I wish to add another filter, which is only for it to add the values in column E if the value is b is correct (as above) and also if column c has a value of 100.
View Replies!
View Related
Record The Total Items From Each Sheet In A Column Called All Totals
I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can. I am running a small store that feeds 13 buildings. On the spreadsheet there are 13 sheets, one for each building. The items will go out monthly. What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings. If I put an attachment that may help out a bit. If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.
View Replies!
View Related
Add Items: Multi Column ListBox: Non-Contiguous Range
i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command Private Sub UserForm_Initialize() Dim contar, j, i As Double Sheets("Historico").Select contar = 0 For j = 1 To 15 linha = j For i = 3 To 65536
View Replies!
View Related
Hide Show Pivot Column Items Based On Date
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded... Sub Button1_Click() Range("B7").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date") .PivotItems("10/30/2006").Visible = False .PivotItems("10/31/2006").Visible = True End With End Sub I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.
View Replies!
View Related
Searching A Column For Specific Text To Return A Number Of Items Found
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ... the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View Replies!
View Related
Copy Unique Items
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 Replies!
View Related
Add Unique Items
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 Replies!
View Related
Creating Drop Down Lists With Same Multiple Items
Looking to create a drop down list on the top of a column that will find all entries with that numerical value. For example, lets say I have four entries with the value 1234 spread out through the columns. When I click the drop down menu, it should only have 1234 once and when I click it, it shows me all the rows with that entry.
View Replies!
View Related
Count Condition Unique Items
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 Replies!
View Related
Subtotal Non-Unique Items By Employee
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 Replies!
View Related
Retrieve Unique Items With 2 Criteria
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 Replies!
View Related
Count Unique Items In Autofilter
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 Replies!
View Related
Counting Unique Number Of Items
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 Replies!
View Related
Counting Unique Items(values Or Text)
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 Replies!
View Related
Add Unique Items To ComboBox Control
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 Replies!
View Related
Counting Items Unique To A Day/customer Combination
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 Replies!
View Related
Setting Up A Validation Of Data Listbox To Provide The Unique Items Within A Range
I am trying to insert a listbox by the way of data validation and would like to only have unique data displayed in it. I was wondering if anybody has done this before or if it is possible. I would like it to remove any and all records that are blank. This is what I have got so far................. VBA Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant ' Accepts an array or range as input ' If Count = True or is missing, the function returns the number ' of unique elements ' If Count = False, the function returns a variant array of unique ' elements Next i AddItem: ' If not in list, add the item to unique list If Not FoundMatch Then NumUnique = NumUnique + 1 ReDim Preserve Unique(NumUnique) Unique(NumUnique) = Element End If Next Element ' Assign a value to the function If Count Then UniqueItems = NumUnique Else UniqueItems = Unique End Function Then I input an array with a few duplicate Item and us the function to determine the list. So far I found that it worked for the following function: {=TRANSPOSE(UniqueItems(A4:A27))} but this only gives me the number of unique items in the array. The problem is when I try to use the following function: {=TRANSPOSE(UniqueItems(A3:A26,FALSE))} This now only returns a zero and if I fill down they all are zero. I would like to get a list of unique items from this formula. Example list would be: {Array = Lorem, Lorem, foo, bar, bar} {Formula_returns = Lorem, foo, bar} I haven't a clue how to display this in a regular excel cell box so I thought that using a validation list box would inherently work.
View Replies!
View Related
|
TRACKER
