Count Unique Values in a Cell - and the Entire Range that Cell Resides In

[Philometis]

I have a worksheet that has a column of cells but each cell has one to several field names. I find lots of guidance on counting unique values in a range of cells BUT not within cell and ranges.

The values in the cells are alphanumeric field names such as COLCOD, A15OB, ACCTNO etc.

The goal if not clear (the worksheet that this column is in has roughly 400 rows) is to count the unique instances of fields in the entire column BUT considering one cell in that column may have several fields as noted above. More specifically one cell might have:

COLCOD
A15OB
ACCTNO

In the above instance I need to return a unique count of 3. But as noted, those same fields could and will be referenced in other cells in the same column, and if those were the only fields, then the unique count of the column of cells would also be 3.

Currently, the fields are not separated by commas and though they happen to lie vertically in the column, that is simply due to the column width, so I may I need to use commas to help delineate a change in fields.

Greatly appreciate the help lads. I have found there to be plenty of brilliant people on this board and I know I can't be the first to have uncovered this need.

Finally, I would prefer the solution be contained within Excel functions since if I introduce VBA I believe it will impair my ability to share the sheet and results with several people that the worksheet gets shared with.

Thanks!

Philo

 

[Philometis]

Hiker,

I started with Aladin's solution and it worked on my home computer (Excel 2010 I believe), but it doesn't work here at the office where we use 2007. I shot him a note but was curious if you knew and whether it impacts your solution. I found some crazy things on the internet like unlocking the cell changing text to columns but nothing is fixing it!

Thanks!

 

[Philometis]

Hiker,

I am now working on your solution and will do both for the challenge, BUT it helps if change your security settings allow macros and VBA!!!!!!!!!!!!!!!!!!!!!

Thanks to you and Aladin - you guys rock - especially since I didn't give you a sample sheet!

I may have some final questions as the count is off but I think it is merely due to the matrix size.

Thanks!

 

[Aladin Akyurek]

Aladin,

This doesn't work in my actual workbook. I will be testing the population of cells, but at this stage, there are many empty cells in the entire range - would that prohibit this from working and if so, is there a method to account for it and fix it?

Thanks again!

Philo

Aladin,

More specifically, the little caution box comes up next to the cell where the formula is placed and says "this formula contains unrevognized text." Hope that helps and thanks again!

Hiker,

I started with Aladin's solution and it worked on my home computer (Excel 2010 I believe), but it doesn't work here at the office where we use 2007. I shot him a note but was curious if you knew and whether it impacts your solution. I found some crazy things on the internet like unlocking the cell changing text to columns but nothing is fixing it!

Thanks!

See the workbook:
https://dl.dropboxusercontent.com/u/65698317/Philometis%20Count%20Unique%20Values%20in%20a%20Cell%20-%20and%20the%20Entire%20Range%20that%20Cell%20Resides%20In.xlsm

 

[Philometis]

Hiker95, I got side-tracked - I had to make some minor changes due to different column of values and the fact that my values start on row2, but I am over by 1 using the attached - I believe I can't final the final change needed so that it starts counting in C2 - I changed the 3rd row to "C2" from "C" but that wasn't it. Thanks!

Sub GetUniqueCount()
' hiker95, 09/06/2014
Dim c As Range, n As Long, s, i As Long
Application.ScreenUpdating = False
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each c In Range("C2", Range("C" & Rows.Count).End(xlUp))
If InStr(c, vbLf) Then
s = Split(c, vbLf)
For i = LBound(s) To UBound(s)
If Not .exists(s(i)) Then
.Add s(i), 1
n = n + 1
End If
Next i
Else
If Not .exists(c.Value) Then
.Add c, 1
n = n + 1
End If
End If
Next c
End With
With Cells(1, 1)
.Value = "Unique Count"
.Font.Bold = True
End With
Cells(4, 3) = n
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

 

[Philometis]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

[TABLE="width: 200"]
<TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 64, bgcolor: #ccc0da"]B[/TD]
[TD="width: 94, bgcolor: #ccc0da"]C[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]Uniq#
[/TD]
[TD="width: 64, bgcolor: #ccc0da"]Table
[/TD]
[TD="width: 94, bgcolor: #ccc0da"]Unique Count
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]RET7b4
[/TD]
[TD="width: 64, bgcolor: #ccc0da, align: left"]LNMAST, LNHIST, LXMAST
[/TD]
[TD="width: 94, bgcolor: #ccc0da, align: left"]USRSTS
STATUS
CALREP
LXSTAT
LHTRAN
LHPST6
COLCOD
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: #ccc0da, align: left"]LNMAST
LNHIST
LXMAX
[/TD]
[TD="width: 94, bgcolor: #ccc0da, align: left"]COLCOD
A15OB
STATUS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]RET7b6
[/TD]
[TD="width: 64, bgcolor: #ccc0da, align: left"]LNMAST, LNHIST

[/TD]
[TD="width: 94, bgcolor: #ccc0da, align: left"]STATUS
CALREP
AE43B1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]9

[/TD]
[/TR]
</TBODY>[/TABLE]


Giant shout out to my teachers Hiker95 & Aladin - the above is a modified (had to add some things for testing etc.) but it is the solution for the above sample Excel sheet TO SOLVE unique occurances of fieldnames (the last column. THANKS AGAIN Hiker 95 & Aladin! The following is the VB:

Sub GetUniqueCount()
' hiker95, 09/06/2014
Dim c As Range, n As Long, s, i As Long
Application.ScreenUpdating = False
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each c In Range("C2:C4", Range("C" & Rows.Count).End(xlUp))
If InStr(c, vbLf) Then
s = Split(c, vbLf)
For i = LBound(s) To UBound(s)
If Not .exists(s(i)) Then
.Add s(i), 1
n = n + 1
End If
Next i
Else
If Not .exists(c.Value) Then
.Add c, 1
n = n + 1
End If
End If
Next c
End With
With Cells(1, 3)
.Value = "Unique Count"
.Font.Bold = True
End With
Cells(5, 3) = n
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

 

[Aladin Akyurek]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Why do we have 3 columns? Which column do you want to run a unique count?

 

[hiker95]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Philometis,

Thanks for the feedback.

You are very welcome. Glad we could help, and, it must feel great to be able to adjust a macro to make it work correctly - nicely done.

And, come back anytime.

 

[Philometis]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Hiker95!

Uh-oh - just got a message - title bar is "Visual Basic" and message underneath is "Object Required"

The actual worksheet is massive, but I know I have this right! Help?

 

[Philometis]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

In the final worksheet as noted below I have over 40 columns - column 3 simply respresents the one I want to do a unique count on - and on the final, I got the mssage I noted below

 

[hiker95]

Re: Count Unique Values in a Cell - and the Entire Range that Cell Resides In - SOLVED

Philometis,

The only way we are going to solve your request, we will have to see your workbook/worksheet, NOT a screenshot.


You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.