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

 

[Aladin Akyurek]

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

Uniq#	Table	Unique Count
		USRSTS STATUS CALREP LXSTAT LHTRAN LHPST6 COLCOD
		COLCOD A15OB STATUS
		STATUS CALREP AE43B1

<COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3953" width=111><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4551" width=128><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6058" width=170><TBODY>
</TBODY>

I'm not sure about the headers you use though...

A1:C4, with C2:C4 the relevant data for which a unique count is required.

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(V(EVAL("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(REPLACE(aconcat(SUBSTITUTE(";"&C2:C4,CHAR(10),";")),1,1,""),";",CHAR(34)&";"),";",";"&CHAR(34))&CHAR(34)&"}"))<>"",MATCH(V(),V(),0)),ROW(INDIRECT("1:"&COUNTA(V())))),1))

 

[hiker95]

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

Philometis,

Sample raw data:

Excel 2007
	A	B	C
1	Uniq#	Table	Unique Count
2	RET7b4	LNMAST, LNHIST, LXMAST	USRSTS STATUS CALREP LXSTAT LHTRAN LHPST6 COLCOD
3		LNMAST	COLCOD A15OB STATUS
4	RET7b6	LNMAST, LNHIST	STATUS CALREP AE43B1
5
Sheet1

After the new macro:

Excel 2007
	A	B	C
1	Uniq#	Table	Unique Count
2	RET7b4	LNMAST, LNHIST, LXMAST	USRSTS STATUS CALREP LXSTAT LHTRAN LHPST6 COLCOD
3		LNMAST	COLCOD A15OB STATUS
4	RET7b6	LNMAST, LNHIST	STATUS CALREP AE43B1
5			9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniqueCount_V2()
' hiker95, 09/08/2014, ME803634
Dim c As Range, n As Long, s, i As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "C").End(xlUp).Row
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In Range("C2:C" & lr)
    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(lr + 1, 3)
  .Value = n
  .Font.Bold = True
  .HorizontalAlignment = xlCenter
End With
Columns(3).AutoFit
Application.ScreenUpdating = True
End Sub

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Then run the GetUniqueCount_V2 macro.

 

[Philometis]

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

Hiker95,

Works like a charm on my "new" test file - now (not that you are waiting on me but your patience is exceptional), I will run on my master file - only if that does not work will I scrub that file of personal data and send it, but it is worth noting that saving this file to a new name and opening it of course makes a macro "warning" button pop up.

I will likely responsd as it "appeared" that the actual file (when diagnosing it), that there ia a "locked" list of of "approved" macros and "authors" on that file. One of many reasons I am paranoid about sending even a scrubbed one.

I owe you and Aladin a case of oranges in Florida but not in season) when this is done. You both make this community! Thanks!

 

[hiker95]

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

Hiker95,

Works like a charm on my "new" test file

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

Please answer the following two questions:

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


How about giving us a workbook with one worksheet, with it's actual worksheet name, and, with just column C setup up exactly like the real thing?


You can upload your workbook to Box Net,

mark the workbook for sharing

and provide us with a link to your workbook.


If you can not show us just the actual raw data in column C in a workbook/worksheet, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Philometis]

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

Hiker95

Ugh. Doesn't seem to be permissions as the last macro license in the file was expired. I only made adjs to your latest code to account for the fact that the data is in row "AG" and the total row is on 426. Data to analyze starts in row 3. Loading it to Box now. What name do I send it to - doesn't like hiker95. Thanks!!!!!!!!!!!

 

[Philometis]

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

Hiker95!

Sorry that I didn't answer the basics - at work, running Office Excel 2007 SP3 MSO... on a PC.

Cheers!

Craig

 

[hiker95]

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

Philometis,

What name do I send it to - doesn't like hiker95.

You do not send it to anyone.


You can upload your workbook to Box Net,

mark the workbook for sharing

and provide us with a link to your workbook.

 

[Philometis]

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

Drop "sucks" and they are trying to bill me for a simple file upload! WT_?

 

[Philometis]

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

Hiker95,

I am also for obvious reasons discussed, shared my file with the world. How do we fix this??? Drop sucks and they are simply not affordable...

 

[hiker95]

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

Philometis,

It's BOX, not Drop, and, BOX is free.

You can upload your workbook to Box Net

mark the workbook for sharing

and provide us with a link to your workbook.


Or, if you can give us screenshots of a smaller dataset:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download