Counting Number Of Unique Numbers Based On Another Variable?
Apr 24, 2014
I run a report that dumps from data from an ERP system. I've attached a sample of this report in excel that has two sheets. A summary and the data.
I am able to report on the total number of Purchase order lines and total purchase orders using various formulas. I am also able to use formulas to report on the number of lines associated with each product in the I column.
However I cannot, how to calculate the number of unique Po# (B Column) numbers based on the Product (I Column) .....
View 14 Replies
ADVERTISEMENT
Dec 15, 2009
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 9 Replies
View Related
Nov 28, 2011
Is there a formula I can use to count the number of unique claim numbers in an excel column?
Is there a way that I can format a cell containing a number containing a decimal point to a number without a decimal point. Example, 205.55 to 20555
View 6 Replies
View Related
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
Feb 10, 2007
I found the following formula online while searching for how to count unique instances of a number in a list. The list may have blank cells or repeated numbers but this formula works. =SUMPRODUCT((A1:100<>"")/COUNTIF(A1:A100,A1:A100&""))
So my question is...can someone verbally explain just exactly what is going on? i.e. I don't understand the <>"" in the sumproduct argument or the &"" in the second argument of the countif.
View 5 Replies
View Related
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
Oct 3, 2007
I have a list of around 50000 names. I need to count how many are unique.
View 9 Replies
View Related
Mar 6, 2003
I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.
View 9 Replies
View Related
Aug 11, 2007
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 4 Replies
View Related
Aug 1, 2008
I have 2 columns that I need to use simultaneously to conclude something.
Column A
Row 1: Dog
Row 2: Cat
Row 3: Bird
Row 4: Dog
Row 5: Dog
Row 6: Dog
Column B
Row 1: See Vet
Row 2: See Vet
Row 3: Get Rest
Row 4: Get Rest
Row 5: Get Rest
Row 6: See Vet
Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
View 5 Replies
View Related
Aug 21, 2014
Formula which will count unique values in column A based on condition in Column B which "y" .....
Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....
View 3 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
May 7, 2007
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.
For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.
You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.
View 9 Replies
View Related
May 4, 2006
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 14 Replies
View Related
Apr 7, 2006
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 9 Replies
View Related
Jun 26, 2013
I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
Project Work Id
8069000
8069000
8069000
8069000.1
[Code]....
View 5 Replies
View Related
Feb 17, 2008
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 5 Replies
View Related
Mar 18, 2008
I am working to develop a spreadsheet that takes user input (from a barcode scanner, that issues a hard return, limiting my information to a single column) in the following order. User ID, this is an Alphanumeric unique ID (AA1AAA) or user id. Followed by a pick ticket ID the value of the pick ticket ID will always be above 100000 (ie 123123) followed lastly by the number of lines on the pick ticket id (this number never exceeds 15)
So an example data set would be:
AA1AAA - User ID
123123 - pick Ticket ID
7 - Number of lines on the ticket
123124 - pick Ticket ID
10 - Number of lines on the ticket
123125 - pick Ticket ID
6 - Number of lines on the ticket
AA1BBB - User ID..........................
View 6 Replies
View Related
Jan 10, 2014
I have three sheets of data, each sheet has various columns of data, but within each sheet, column one contains a unique reference number (URN), which is a unique six digit number identifying an organisation.
In sheet two I need to create a new column and call this 'Filter Group'. In that column I want to insert a series of IF statements to return a value from all three sheets. The difficulty I'm having is that I can't get the if statements to work specifically for when the URN in sheet one or three match the URN in sheet two.
Sheet two is my main data sheet. Sheet one is called '2 Weeks Previous' and sheet three is called '2 Weeks ahead'. The IF statement I have created so far is as follows:
=IF('2 Weeks Previous'!H:H=1,"ENF grade 1",
IF('2 Weeks Previous'!H:H=2,"ENF grade 2",
IF('2 Weeks Previous'!H:H=3,"ENF grade 3",
IF('2 Weeks Previous'!H:H=4,"ENF grade 4",
IF('2 Weeks Ahead'!B:B<>"",'2 Weeks Ahead'!G:G,
IF('Background data'!H:H=2,"All-Good","NotRequired"))))))
View 8 Replies
View Related
Nov 20, 2008
I have list of unique numbers on Column A and names on Column B. I have another set of unique numbers on Column C and counters on Column D. I have 100 numbers on Column A and I have 10 numbers on Column C. Every numbers on Column C exists on Column A. How do I match and align the unique numbers and have Column B and D in same row? (E.g. 100000 Jones 30). This question is very similar to the one discussed at Aligning Columns With Same Data?
View 6 Replies
View Related
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
View 10 Replies
View Related
Jun 17, 2014
I have the following:
A1 = Date item is due. (DD/MM/YY format)
A2 = Time the item was delivered (HH:MM:SS format)
B1 = Actual Date item was delivered.
B2 = Actual Time item was delivered.
I'd like C1 to count the number of days the item was late. - taking into account that 16:00:00 is the deadline on each date.
For example:
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 17/06/14 @ 16:01:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 15:00:00
Days Late = 1
Due Date: 17/06/14 @ 16:00:00
Arrival Date: 18/06/14 @ 16:01:00
Days Late = 2
View 3 Replies
View Related
Jul 27, 2009
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 9 Replies
View Related
Feb 22, 2011
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
View 4 Replies
View Related
May 2, 2008
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335
AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335
View 3 Replies
View Related
Oct 19, 2009
I need the variable here to be 7 numbers long, if not I need to add zero's at the beginning. However the problem I am having here is when then number of zero's is greater than one.
Basically in the example below the variable 'Zeros' = 2 so I need the variable 'Variable' to give answer "00", how do I do this!?
If Len(ActiveCell.Value) 7 Then
Zeros = 7 - Len(ActiveCell.Value)
Variable = CStr("0") * Zeros
End If
View 10 Replies
View Related
Jun 23, 2014
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
Question2.xlsx‎
View 9 Replies
View Related
Oct 5, 2007
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
ex) 1.751
2.52
3.753
418
417
416
View 5 Replies
View Related
Sep 19, 2009
Here’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
View 10 Replies
View Related
Jan 11, 2012
how would you do the following in excel 2007:
"SUM numbers within a cell based on unique strings in another cell".
For example, how would i use formula to SUM the following numbers (and only the numbers for david and sam only once), 700+454+50+40+2+129+16
700david
700 david
454john
50buch
40daniel
2sam
2sam
129mike
16steve
View 9 Replies
View Related
