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

On Monday, September 10, 2012 9:44:29 AM UTC-4, wrote:
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? The tools are identified with a 4 digit number ie. 1204. thanks in advance.

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

On Monday, September 10, 2012 1:30:21 PM UTC-4, (unknown) wrote:
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 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

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

On Mon, 10 Sep 2012 06:44:29 -0700 (PDT), wrote:

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.

On Monday, September 10, 2012 3:50:06 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 10 Sep 2012 06:44:29 -0700 (PDT), wrote:



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.

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.

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

On Mon, 10 Sep 2012 13:07:19 -0700 (PDT), wrote:

On Monday, September 10, 2012 3:50:06 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 10 Sep 2012 06:44:29 -0700 (PDT), wrote:



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.

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.

On Monday, September 10, 2012 9:00:09 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 10 Sep 2012 13:07:19 -0700 (PDT), wrote:



On Monday, September 10, 2012 3:50:06 PM UTC-4, Ron Rosenfeld wrote:

On Mon, 10 Sep 2012 06:44:29 -0700 (PDT), wrote:







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.



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.

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

On Mon, 10 Sep 2012 19:23:30 -0700 (PDT), wrote:

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.

On Monday, September 10, 2012 10:40:55 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 10 Sep 2012 19:23:30 -0700 (PDT), wrote:



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.

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

On Mon, 10 Sep 2012 19:23:30 -0700 (PDT), wrote:

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.

On Monday, September 10, 2012 10:58:22 PM UTC-4, Ron Rosenfeld wrote:
On Mon, 10 Sep 2012 19:23:30 -0700 (PDT), wrote:



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.

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

On Tue, 11 Sep 2012 06:31:50 -0700 (PDT), wrote:

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",T ools,""))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.