Count Unique Values Based On Another Column
I am using Excel 2007, and I'm stuck with this problem.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count/Sum Unique Values Based On Criteria
I'm facing a big problem and I can't found a solution for days. I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below: =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) Let me Put to you an example for you been able to understand my problem. Example considering Range "C2:C20" exluding blank cells (1) - To count unique values =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")) (2) - To count unique values based on criteria in range "B2:B20" =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")) (3) - To sum unique values on "E2:E20" based on same criteria =SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20)) They all work fine. But I need to insert a merge at Row 10 for better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's. I just don't know what to do guys ....
View Replies!
View Related
Count Unique Values Based On Matching Criteria
I have a worksheet with two tabs. First Tab Account Name Account Number Second Tab Account Name Account Number Account Ship Location Number On the first sheet each account name and number only appears once. On the second sheet there may be multiple entries for each Account Name and Number. On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
View Replies!
View Related
How To Count The Number Of Unique Values In A List, Based On Another Value
I need to count the unique values in a list based on another value. i.e. AB ___ Jones AB ___ Smith BC ___ Henry AB ___ Jones BC ___ Brown BC ___ Henry AB ___ Tomkins For all the AB's in column 1, I need a formula that will give me all the unique names from column 2, in this case 3. I've tried adapting: =IF(LEN(June!D5:D100),SUMPRODUCT(1/COUNTIF(June!D5:D100,June!D5:D100))) but haven't been able to get it to work.
View Replies!
View Related
Count Unique Values In A Filtered Column
I need to count the number of unique names from a column of filtered text. On the attached example D5 is where I need the value, the current array I have does not work when the filter is applied by changing the product in drop box. I need to have a count of the unique account names from column A.
View Replies!
View Related
Count Unique Cells Based On Ajacent Cells Having Values
I have the following data in 1 of the tabs. [TABLE] ClassSequenceAB 10SE132422 20SE23212 20SE321 20SE23425 10SE332455 15SE132412 10SE234 [/TABLE] I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows: [TABLE] ClassAB 1032 1511 2012 [/TABLE] Can this be achieved through a formula?
View Replies!
View Related
Count Column W/blanks & Values Based On Specific Date
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View Replies!
View Related
Count Unique Values, For Specific Values
I have an array that I'm using - and it works perfectly: =SUM(If((FREQUENCY(If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""),If(LEN(B10:B100)>0,MATCH(B10:B100,B10:B100,0),""))>0),1)) Now, what I would like to do, is see how many of these unique numbers relate to another number in column D See attachment.
View Replies!
View Related
Count Unique Values ....
So here is my dilemma and I haven't been able to find anything to exacly help me out: I have a large (100k+ records) sheet of sales invoice details. I need to add a total item line per invoice number to each record ....
View Replies!
View Related
Count Unique Values
Card Number XXXX5512 XXXX5512 XXXX4211 XXXX4510 XXXX6134 XXXX9035 XXXX6002 XXXX8653 XXXX7826 XXXX8819 XXXX8315 XXXX0274 I'm looking for a formula to generate the number of unique card numbers are in the column...I'm using this daily, simply pasting the new data in so if I want to keep it as automated as possible.
View Replies!
View Related
Find And Count Unique Values
I have a spreadsheet that holds customer information. What I want to do is find how many customers there were last month. I have a cell (C1) which has last month displayed as 2009/05. A1 holds the customer ID and B1 holds the date they used us. Each customer may has used us many times and I'm having a nightmare trying to solve this.
View Replies!
View Related
Count Unique Values With Critera
I have a large list of data and need to filter unique values of 1 column with a criteria. For example: A B Car 1 Bike 1 Car 2 Car 2 Bike 3 Bike 3 Bike 4 Car 4 I want to filter out only the lines whereby the column A & B is the same comment/value. Other words: both column A & B have to be unique values. Based on the list above this means that the list should look like this: A B Car 1 Bike 1 Car 2 Bike 3 Bike 4 Car 4
View Replies!
View Related
Count Of Unique Values With Duplicates
Good Morning Peps (Oh thats for users in Western Europe, got to get this right!) I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...
View Replies!
View Related
Count Unique Values Under The Influence Of Different Variables
My data: four variables A,B,C and D. A growing dataset representing a group of people being nominated in different weeks for different jobs in different locations. Within a week and over several weeks an individual may be nominated for any number of jobs, in any number of locations. A=week number (52 possibilities), B=job code (20 possibilities), C=location (2 possibilities), D=name (500+ possibilities) .....
View Replies!
View Related
Count Unique Values In A Pivot Table
I have a spreadsheet with several records for each person's name. I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name. When I set up a basic Pivot, it counts each occurence of the person's name.
View Replies!
View Related
Count Unique Values In Pivot Table
I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed...
View Replies!
View Related
Count Of Unique Values In Pivot Table
I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.
View Replies!
View Related
Count Unique Values, And Also Average The Time Per Assignment
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment. Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes. I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View Replies!
View Related
Count Of Unique Numbers Based On A Criteria
My data is like: A1: 5 -- B1: X A2: 5 -- B2: X A3: 5 -- B3: Y A4: 3 -- B4: Y A5: 3 -- B5: X A6: 8 -- B6: Y A7: 2 -- B7: X A8: 3 -- B8: Y A9: 1 -- B9: X I want the count of unique numbers in column A based on the criteria i have on column B. So the count for X should be 4 and for Y should be 3.. Can i achieve this using formula only?
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
Count Unique Records With Criteria From Another Column
As the title says, I need to count the number of unique records (names) in column A, where column L is = to something specific (X,Y,Z,W, whatever) for some statistics im trying to report. Please see the attached spreadsheet. For role X statistics, I need a count of the unique names from col A, where col L = X. Based on my sample spreadsheet, the number should be 2. For Role Y, it should be 3. There is the potential for spaces in the rows, and no one will be 'cross role' I've got a few different methods to just count unique values with specific criteria in the same column, but I just can't make anything work for specific criteria in another column.
View Replies!
View Related
Count The Number Of Unique Numbers In A Column?
I have a column where I have record numbers. The record number can end up duplicate in some cells. PROBLEM_ID IBM-02204732 IBM-03252238 IBM-03252238 IBM-03335648 IBM-03534918 IBM-03534918 IBM-03862015 IBM-03862015 IBM-12737629 IBM-12737629 IBM-17269762 I would like the get a formula that can count the number of unique numbers in that column? I would like to get if possible to get a formula that summarize the number into a single cell.
View Replies!
View Related
Count The Number Of Unique Numbers In A Column
i'm currently trying to count the number of unique numbers in a Column using this formula: =SUMPRODUCT((A3:A3002<>"")/COUNTIF(A3:A4002;A3:A3002&"")) So far so good.....But I want to add some conditions like only count the numbers that meet the condition say N1:N3000="SE*" =SUMPRODUCT((A1:A3000<>"");(AND(OR(N1:N3000="SE*";N1:N3000="INC*")/COUNTIF(A1:A3000;A1:A3000&"")))) On this one I get #VALUE! and i can't figure out why. I have also tried to put the AND/OR condition before the actual starting of the array of the SUMPRODUCT but nothing.
View Replies!
View Related
Capture Unique Values From 1 Column And Populate The Result Into Another Column
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?.
View Replies!
View Related
Count Unique Words Appearing In A Column Once Per Row
I would like to modify the below macro so it only counts a word once even if it appears more than once in a row. The reason I want to do this is so I can get a snapshot of the data without certain terms being over-represented. For example, in the attached example the word 'Microsoft' appears 5 times in row 4 but I only want this to be counted once. In the whole data set Microsoft appears 20 times but only in 7 of the 20 rows so I would like the count to be 7. The example is set out as follows: In column B there is a description field which in practice will contain consumer complaints and inquiries. To keep the data anonymous the description is filled out with random words and all other columns are blank. The output of Andy Pope's unique word counting macro appears in I:J.
View Replies!
View Related
Count Unique Entries In One Column That Meet Conditions
I tried to ask this question yesterday -- but it was a follow-up question stuck at the bottom of a thread. So, with your indulgence, here is a simpler version of the question, complete with an attached spreadsheet, if you wish to use it. I also closed the other thread by marking it "Solved", since it answered my initial question.] The situation: I have two columns of data. The data is not in alphabetical order, and every column includes duplicate values. namegender jones m martinf smithf collinsf wilsonm jones m martinf hughesm wilsonm martinm smithf west f jones m west f martinm The challenge: In one cell, count the number of unique names that appear in the name column 3 or more times... with the additional condition that each unique name (which appears at least 3 times) must include at least one one woman! The correct result: ...
View Replies!
View Related
Lookup Based On Two Unique Values
I've been searching both here and on google but I've been unable to make use of any of the numerous suggestions and examples I've found to do this; given I'm right in thinking that this should be done with a vlookup! I have a table in which different customers pay different prices for different products. C13 is a cell in which I want Excel to look up the correct value based on A13 and B13 (in this case C3). I have no experience with lookup functions, which might be the reason why I can't get this to work. I bet it's pretty simple
View Replies!
View Related
Sumproduct (count The Number Of Unique, Or Distinct, Company Names In Column)
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count. I should also point out that most company names appear multiple times in column [C7]. a) Count the unique company names in column [C7]... b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
View Replies!
View Related
Counting Unique Values Based On A Variable
I'm having difficulty trying to write a formula for the following type of data: [data] .... This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...
View Replies!
View Related
Counting Unique Values Based On 2 Criteria
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............ I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View Replies!
View Related
Pulling Unique Values From A Column
HOW TO pull the unique values out of a column of entries and put them into another column. For example I'll have a list of maybe 2,000 but with only 1-7 different values in the column, what I want to do is populate another column with only the list of the 1-7 values that appear in the larger set.
View Replies!
View Related
Counting Unique Values In A Column?
How can I count the number of unique values in a column? I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date. On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet. So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.
View Replies!
View Related
Array Of Only Unique Column Values
I need to capture a list of unique usernames appearing in a column on the sheet, and also the number of times each user has a desired value occur for their row (an error). The number of users and the names of the users will change from one sheet to the next making a static solution unworkable. The user names will be unsorted in the column. My first reflex is to use an array, and an associative array if possible would allow me to reference the user by name, instead of a clumsier index number and iterating through a loop to verify if the user is already stored in the array. I don't know if it's possible in VBS though. I'll add any new users names to this array if they aren't already in there.
View Replies!
View Related
Find Unique Values Under A Column
In an excel sheet i have around 10 columns. I filtered the data based on the column1 using vba. Then after that, the filtered data in column2 have repeat finance accounts. What i need to find the unique finance accounts under the column2. Is there any methods to find the unique value under the column2?
View Replies!
View Related
Copying A Row Based On Matching Unique Values
I have the following code which takes a unique value from 'sheet 1', and searches for the value in 'sheet 2'. If there is a matching value in sheet 2 it will copy the full row into sheet three. I need to extend the code so that all values from 'sheet 1' are copied to 'sheet 3' even if there is no matching value in 'sheet 2'. If there is a matching value in 'sheet 2' however I need the entire row to be copied to 'sheet 3' Sub moving() Dim c As Range, d As Range Worksheets("Sheet2").Activate For Each d In Worksheets("Sheet1").Range("A1:A500") For Each c In Range("A1:A15") If d = c Then c.Resize(1, 70).Copy Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues Exit For End If Next Next Application.CutCopyMode = False Worksheets("Sheet3").Activate Range("A1").Select End Sub
View Replies!
View Related
Fill ComboBox With Unique Values From Column
I need to create a drop-down (or a combo list) list based on a column in a separate worksheet (in the same workbook). The values in the column have many duplications, and I simply want the combo box to return a unique list. After trying this using various options like drop-down boxes, combo boxes etc it does not seem to work. A drop-down box will return a complete list (including all of the duplicates). In addition the drop-down was linked to a cell and the value returned to the cell was a number based on the position in the list when what I actually wanted was the text. For example - my list in Worksheet 2: PETS Cat Dog Dog Cat Horse Rabbit... In Worksheet 1 if I choose 'Dog' from my Combo box (or drop-down) along with showing all of the duplicates it returns '3', instead of what I want 'Dog' and no duplicates.
View Replies!
View Related
Create New Workbook For All Unique Values Of Column
this code "Creates a New Workbook" for each item listed in an excel table, then, copy all unique values in their respective Workbook and save it. So, the problem is this. instead of creating a "New Workbook", i want to open a previously created Workbook (template) and do the procedures listed above. Is this posible? Sub Copy_To_Workbooks() Dim CalcMode As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Dim WSNew As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Dim foldername As String Dim MyPath As String Dim FieldNum As Integer Dim FileExtStr As String Dim FileFormatNum As Long Set ws1 = Sheets("Sheet1") '<<< Change If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else If ws1.Parent.FileFormat = 56 Then FileExtStr = ".xls": FileFormatNum = 56 Else FileExtStr = ".xlsx": FileFormatNum = 51 End If End If..............
View Replies!
View Related
Filtering For Unique Entries Based On One Column
This may be a basic question but I haven't been able to find the solution: I'm using Excel 2003. I have a list with repetitive entries in one column and unique entries in the other columns. Example 0001 a 0001 b 0002 c 0002 d 0003 e 0003 f I need to filter the list so that only unique entries in the first column show up, but I need the rest of the information to carry with it. Example: 0001 a 0002 c 0003 f I can't use the Avanced Filter > Unique Entries Only function because the information in the adjoining columns is not unique.
View Replies!
View Related
Extract Unique Records Based On 1 Column
I have a single excel data sheet with 10 rows of header information and then multiple rows and columns of data I need to extract the 10 rows of header data plus the rows for each unique record in Col A into its own separate worksheet, with the work sheet name being the unique record from Col A To further add to the challenge, the data in col A may have "/" in so will not comply with excel sheet naming convention so would like an error message to remind me to manually change a sheet name. I attach an dummy data sheet just to show what I mean!
View Replies!
View Related
For Each Unique Value In Col A+B, Removing Lines Based On Column C
For each unique value of Order Number [col A] + Line Number [col B], I need a program which will locate the first instance of Next Stat [col D] = 530 and the first instance of col D = 540, deleting the other lines. The output will have two lines remaining for each unique col A+B combination, one where col D = 530 and another = 540. The data will always be sorted in date/time order prior to running this program. The actual data set has a varying number of lines, usually 1000+.
View Replies!
View Related
Return Unique ID Based On Multi-Column Lookup
I'm looking for a formula (VBA I'm assuming) that will help me create a unique customer ID out of data that my website generates in order to import records into my accounting system. I have a current list of customers in CSV format with the columns: CustomerID, CustomerName, CustomerZip Each customer in our accounting system is assigned a unique,7 digit CustomerID in the format of XXX#### where XXX are the first 3 letters of their last name, and #### is a 4 digit number (with leading 0's) to create unique ID's for customer with the same first 3 characters of their last name. SAMPLE LIST: SCH0001, Lindsey Schubert, 75230 SCH0002, Thomas Schoembs, 53132 ADA0001, Samantha Adams, 28205 ... What I'd like to do is pass the formula 3 parameters (Cust_First_Name, Cust_Last_Name, Zip) and have it parse the .CSV file and either return an existing customer's current ID or generate the appropriate new, unique ID, making sure in increase the 4 digit # accordingly and insert leading 0's if necessary. Another caveat, if possible to work with, is the ability to also pass the formula another range of cells to append to the end of the .CSV file's data for comparison reasons. There are times when I'll bulk-import orders (or we receive numerous in the same batch) and the potential exists to have two customers that would have the same CustomerID created using JUST the .CSV data. Ie. If we use the example above and have new customers of Steve Schwab and Julie Schwitzer - we'd end up incorrectly assigning them both SCH0003, where if we'd read Steve Schwab's newly created info and customer ID of SCH0003, then Schwitzer would correctly be assigned SCH0004.
View Replies!
View Related
|
TRACKER
