K

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

K

K

B

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

K

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?

K

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.

B

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

K

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

K

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.

K

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.