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

 

[hiker95]

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

Philometis,

And, you may want to review the forum rules:

http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Philometis]

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

Hiker95,

Thanks for your patience and help! Finally loaded the file to Box per my pm including link. All it took was a good night of sleep!

Can't thank you enough!

Philo

 

[hiker95]

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

Philometis,

Thanks for the Private Message.

When I attempt to download/open the workbook I get the following message:

Excel found unreadable content in 'BI-LSDW Jack Henry Working Matrix 20140909 Test 2.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.


You may want to try with a smaller workbook, and, one that does not contain any macros.

 

[Philometis]

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

Hiker,

Just resent the file via PM sans macro etc so that should do it. Thanks so much!

Philo

 

[hiker95]

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

Philometis,

Your last two Private Messages, returned the same error message when I attempted to open/download the workbooks?????

 

[hiker95]

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

Philometis,

One last try:

How about another workbook on BOX, with only the column in question in the worksheet?

And, without any macros.

 

[Philometis]

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

Hiker!

Will do and thanks for your patience - let me wrap some sql up and shoot it over this pm.

Thanks!!!

Craig

 

[hiker95]

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

Philometis,

Your last Private Message, returned the same error message when I attempted to open/download the workbook.

 

[Philometis]

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

Hiker,

That is bizarre as I eliminated everything (I just got done with my SQL coding this ties to). Let me look for something - as there was that old macro with an expired date tied to the worksheet, but I swear I eliminated that and everything else. I promise it is a clean file, but let me look at it right now!

Thanks!

Craig

 

[Philometis]

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

Hiker,

Just sent you a PM with a completely new, clean file so I don't waste any more of your time. In the vein of this board, I would like to post a cleaned up and shortened version when the time is right.

Thanks for your patience and help!

Craig