countif - unique values only

[kijijijt]

I think there is a way to do this but having a brain fart at the moment. I have 3 columns, say A-B-C. First 2 are part types and the 3rd list tools. Certain tools go with certain parts indicated by an X in column A or B or both. The tools can be listed multiple times, even for the same part type. I would like to know how many unique tools belong to each part. If I use countif it returns all tools used for a certain part including duplicates. Is there a way to count only unique tools with no duplicates? thanks in advance..

 

[kijijijt]

I think there is a way to do this but having a brain fart at the moment. I have 3 columns, say A-B-C. First 2 are part types and the 3rd list tools.Certain tools go with certain parts indicated by an X in column A or B or both. The tools can be listed multiple times, even for the same part type. I would like to know how many unique tools belong to each part. If I use countif it returns all tools used for a certain part including duplicates. Isthere a way to count only unique tools with no duplicates? The tools are identified with a 4 digit number ie. 1204. thanks in advance.

 

[benmcclave]

Short of adding columns to the side of the data or using Data Validation toremove duplicates, I could only solve this one using a User-Defined Function (UDF). The UDF I wrote takes two arguments. Range1 is the range containing x's (column A in your example). Range2 is the range containing tools (column C in your example). The code will first check that the ranges used are only one column each and contain the same number of cells. Then, it will use a counter to run through each cell in both ranges tabulating the unique values. Since this code will run on the entire range of cells entered,I would avoid feeding it an entire column of data. The code will run muchquicker if a specific range is used (even a large one of 50,000 cells is noticably quicker than an entire column).

To use this function, paste the code to a new module and enter the formula into a cell on your sheet. For example, if you wanted to count the unique number of tools (column C) for part 1 (column A), you would enter:

=UniqueCount(A2:A5000,C2:C5000)

Hope this helps,

Ben

Function UniqueCount(Range1 As Range, Range2 As Range)
Dim l As Long
Dim lCount As Long

lCount = 0

If Range1.Count <> Range2.Count Then
UniqueCount = "Range sizes must be same size"
Exit Function
ElseIf Range1.Columns.Count + Range2.Columns.Count <> 2 Then
UniqueCount = "Ranges must contain only one column each"
Exit Function
End If

For l = 1 To Range1.Count

If Range1.Range("A" & l).Value <> "x" Then
lCount = lCount + 0
Else
If WorksheetFunction.CountIfs(Range1.Range("A1:A" & l), "x", _
Range2.Range("A1:A" & l), Range2.Range("A" & l)) > 1 Then
lCount = lCount + 0
Else
lCount = lCount + 1
End If
End If

Next l

UniqueCount = lCount

End Function

 

[kijijijt]

Short of adding columns to the side of the data or using Data Validation to remove duplicates, I could only solve this one using a User-Defined Function (UDF). The UDF I wrote takes two arguments. Range1 is the range containing x's (column A in your example). Range2 is the range containing tools(column C in your example). The code will first check that the ranges used are only one column each and contain the same number of cells. Then, it will use a counter to run through each cell in both ranges tabulating the unique values. Since this code will run on the entire range of cells entered, I would avoid feeding it an entire column of data. The code will run much quicker if a specific range is used (even a large one of 50,000 cells isnoticably quicker than an entire column).



To use this function, paste the code to a new module and enter the formula into a cell on your sheet. For example, if you wanted to count the unique number of tools (column C) for part 1 (column A), you would enter:



=UniqueCount(A2:A5000,C2:C5000)



Hope this helps,



Ben



Function UniqueCount(Range1 As Range, Range2 As Range)

Dim l As Long

Dim lCount As Long



lCount = 0



If Range1.Count <> Range2.Count Then

UniqueCount = "Range sizes must be same size"

Exit Function

ElseIf Range1.Columns.Count + Range2.Columns.Count <> 2 Then

UniqueCount = "Ranges must contain only one column each"

Exit Function

End If



For l = 1 To Range1.Count



If Range1.Range("A" & l).Value <> "x" Then

lCount = lCount + 0

Else

If WorksheetFunction.CountIfs(Range1.Range("A1:A" & l), "x", _

Range2.Range("A1:A" & l), Range2.Range("A" & l)) > 1 Then

lCount = lCount + 0

Else

lCount = lCount + 1

End If

End If



Next l



UniqueCount = lCount



End Function

thanks for the code...it seems to work but can you adjust it to ignore blank cells?

 

[Ron Rosenfeld]

I think there is a way to do this but having a brain fart at the moment. I have 3 columns, say A-B-C. First 2 are part types and the 3rd list tools. Certain tools go with certain parts indicated by an X in column A or B or both. The tools can be listed multiple times, even for the same part type. I would like to know how many unique tools belong to each part. If I use countif it returns all tools used for a certain part including duplicates. Is there a way to count only unique tools with no duplicates? thanks in advance.

Your data setup is unclear to me. If you have an "X" in column A or Column B, or both, how do you determine the part type to which this refers? Perhaps some examples of various lines would be of value.

 

[kijijijt]

Your data setup is unclear to me. If you have an "X" in column A or Column B, or both, how do you determine the part type to which this refers? Perhaps some examples of various lines would be of value.

the same tool can be used for both part types indicated by an "X" in columnA and B. In another case, I may have a tool only used for one part type indicated by an "X" in the appropriate column. However in some cases the toolis listed multiple times. I would like to count how many unique tools I have for each part type. thanks.

 

[benmcclave]

thanks for the code...it seems to work but can you adjust it to ignore blank cells?

Sure thing, try this:

Function UniqueCount(Range1 As Range, Range2 As Range)
Dim l As Long
Dim lCount As Long

lCount = 0

If Range1.Count <> Range2.Count Then
UniqueCount = "Range sizes must be same size"
Exit Function
ElseIf Range1.Columns.Count + Range2.Columns.Count <> 2 Then
UniqueCount = "Ranges must contain only one column each"
Exit Function
End If
Debug.Print lCount
For l = 1 To Range1.Count

If Range1.Range("A" & l).Value <> "x" Then
lCount = lCount + 0
Else
If Range2.Range("A" & l) = "" Then
lCount = lCount + 0
ElseIf WorksheetFunction.CountIfs(Range1.Range("A1:A" & l), "x", _
Range2.Range("A1:A" & l), Range2.Range("A" & l)) > 1 Then
lCount = lCount + 0
Else
lCount = lCount + 1
End If
End If
Debug.Print lCount & ": " & Range1.Range("A1:A" & l).Address
Next l

UniqueCount = lCount

End Function

 

[Ron Rosenfeld]

the same tool can be used for both part types indicated by an "X" in column A and B. In another case, I may have a tool only used for one part type indicated by an "X" in the appropriate column. However in some cases the tool is listed multiple times. I would like to count how many unique tools I have for each part type. thanks.

I am still not understanding.

This is your set up as I understand it: X in A, X in B Tool in c

A B C
1 x x tool 1
2 X tool 2
3 x tool 3
4 x x tool 3

What are the part types?

Oh well, I see that you have an answer from benmclave, so he must be able to understand your layout.

Unless a part number is defined by the pattern of the x's, in which case there could be only 3 or four part numbers, which doesn't seem to make sense.

 

[kijijijt]

I am still not understanding.



This is your set up as I understand it: X in A, X in B Tool in c



A B C

1 x x tool 1

2 X tool 2

3 x tool 3

4 x x tool 3



What are the part types?



Oh well, I see that you have an answer from benmclave, so he must be ableto understand your layout.



Unless a part number is defined by the pattern of the x's, in which case there could be only 3 or four part numbers, which doesn't seem to make sense.

the part numbers are at the top of the column. lets call it part A and partB. on your layout part A requires tools 1,2 and 3 and part B requires tool1,3

 

[Ron Rosenfeld]

the part numbers are at the top of the column. lets call it part A and part B. on your layout part A requires tools 1,2 and 3 and part B requires tool 1,3

In your original post, you wrote you had three columns A - B - C with the tools in Column C. That would imply only two part numbers based on what you've written above. Surely you must have more than two part numbers, if this is a real life problem. And, if so, it seems unlikely that your tools are listed in column C. Please, how is your data really set up? That information will make crafting a solution much more efficient.

 

[kijijijt]

In your original post, you wrote you had three columns A - B - C with thetools in Column C. That would imply only two part numbers based on what you've written above. Surely you must have more than two part numbers, if this is a real life problem. And, if so, it seems unlikely that your tools are listed in column C. Please, how is your data really set up? That information will make crafting a solution much more efficient.

I have a solution from Ben, but thank you for your consideration.

 

[Ron Rosenfeld]

I would like to know how many unique tools belong to each part. ....
the part numbers are at the top of the column. lets call it part A and part B. on your layout part A requires tools 1,2 and 3 and part B requires tool 1,3

Given your stated setup so far, you can do this with a formula (adapted from http://www.cpearson.com/Excel/Duplicates.aspx )

I Defined some names to use in these formulas:

Tools = $C$2:$C$n (the list of tools)

Part1 = the same range as tools but referring to column A
Part2 = the same range as tools but referring to column B.

I actually used a dynamic reference to name them, assuming there are no blanks in the "Tools" column

Tools =OFFSET(Sheet2!$C$1,1,0,COUNTA(Sheet2!$C:$C)-1)
Part1 =OFFSET(Tools,0,-2)
Part2 =OFFSET(Tools,0,-1)

This formula must be **array-entered**:

=SUM(IF(FREQUENCY(IF(LEN(IF(Part1="x",Tools,""))>0,MATCH(IF(Part1="x",Tools,""),
IF(Part1="x",Tools,""),0),""), IF(LEN(IF(Part1="x",Tools,""))>0,MATCH(IF(Part1="x",Tools,""),
IF(Part1="x",Tools,""),0),""))>0,1))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[kijijijt]

Given your stated setup so far, you can do this with a formula (adapted from http://www.cpearson.com/Excel/Duplicates.aspx )



I Defined some names to use in these formulas:



Tools = $C$2:$C$n (the list of tools)



Part1 = the same range as tools but referring to column A

Part2 = the same range as tools but referring to column B.



I actually used a dynamic reference to name them, assuming there are no blanks in the "Tools" column



Tools =OFFSET(Sheet2!$C$1,1,0,COUNTA(Sheet2!$C:$C)-1)

Part1 =OFFSET(Tools,0,-2)

Part2 =OFFSET(Tools,0,-1)



This formula must be **array-entered**:



=SUM(IF(FREQUENCY(IF(LEN(IF(Part1="x",Tools,""))>0,MATCH(IF(Part1="x",Tools,""),

IF(Part1="x",Tools,""),0),""), IF(LEN(IF(Part1="x",Tools,""))>0,MATCH(IF(Part1="x",Tools,""),

IF(Part1="x",Tools,""),0),""))>0,1))



----------------------------------------



To **array-enter** a formula, after entering

the formula into the cell or formula bar, hold down

<ctrl><shift> while hitting <enter>. If you did this

correctly, Excel will place braces {...} around the formula.

can you revise the formula to work if I have blanks in the tools column as I do have a case where there may be an "x" in the parts column and not necessarily a tool

 

[Ron Rosenfeld]

can you revise the formula to work if I have blanks in the tools column as I do have a case where there may be an "x" in the parts column and not necessarily a tool

=SUM(IF(FREQUENCY(IF(LEN(Tools)*LEN(IF(Part1="x",Tools,""))>0,
MATCH(IF(Part1="x",Tools,""),IF(Part1="x",Tools,""),0),""),
IF(LEN(Tools)*LEN(IF(Part1="x",Tools,""))>0,MATCH(IF(Part1="x",Tools,""),
IF(Part1="x",Tools,""),0),""))>0,1))

Note that in NAMEing the ranges, you must select the ranges (or manually enter the references), since the method I used in the previous posting assumed no blanks in Tools.