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]

XOR,

Sorry for the late response. You are exactly correct - alt+enter is what "stacks" the values in each cell. I'm interested in hearing your solution. The others work via macros so it solves the problem, but in the vein of learning more, I would like to hear if you have a different solution...

Thanks!

Philo

 

[Aladin Akyurek]

...The others work via macros so it solves the problem...

My suggestion is technically not a macro solution, though.

 

[XOR LX]

No worries.

Perhaps you can have a go at adapting the solution I give here:

List of unique entries from column of space-separated strings | EXCELXOR

That's precisely the same as in your situation though with spaces as delimiter, not linebreaks.

Of course you're not actually interested in producing a list, simply a count. But if you read the first few paragraphs or so, you'll realise that the count forms part of that solution, and is given as a separate formula.

It's the array formula in C1 bit you'd want. And you'd need to make all the additions to Name Manager, as I describe. You don't need to bother with the rest of the article (though it's quite interesting, if I say so myself!).

All you'd need to do is to replace all occurrences of " " in all of those formulas with CHAR(10).

Perhaps if you have a go and then get back to me if you're struggling?

Cheers

 

[hiker95]

Philometis,

Another brilliant and perfect response. Thanks so much hiker95!

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

You guys are leaving me with some homework!

See if the below link to my most up to date list will help you:

Training / Books / Sites as of 8/30/2014

http://www.mrexcel.com/forum/excel-...anging-columns-rows-advanced.html#post3925232

 

[Philometis]

Aladin,

Thanks for the clarification - am I correct in saying it is a "user defined function?"

Thanks again, and say hey to Robert van der Laan - a partner of mine from NL.

Philo

 

[Philometis]

XOR,

Will definitely test this out. Some great and intelligent chaps on this board / site. Thanks and I'll get back to you.

Philo

 

[Aladin Akyurek]

Aladin,

Thanks for the clarification - am I correct in saying it is a "user defined function?"

The three functions, v(), eval(), and aconcat(), are user-defined functions. But, not the formula, if I may say so.

Thanks again, and say hey to Robert van der Laan - a partner of mine from NL.

Philo

I'm afraid I don't know him...

 

[Philometis]

I was kidding - the NL isn't that small! Thanks again for your help!

 

[Philometis]

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

 

[Philometis]

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!