*This is a guest post by David Hager. You can learn more about David on LinkedInd*

There has recently been interest in handling row data that contain delimited strings. An elegant solution to split a delimited row into multiple rows using DAX queries can be found here.

http://www.sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html

Another question was asked on LinkedIn about how to count the number unique items of a delimited string. I could not figure out how to do this in PowerPivot, and I am not sure it can be done with a single formula. However, I undertook the challenge to do this in Excel and I came up with a solution, albeit a rather lengthy one.

So, if the string a,c,d,a,b,c,d,e,f,g,h,I,j is in cell A1, the following formula will return the value 10, which is the number of unique items in the delimited string.

In order to explain how this formula works, a simplified version is shown below:

The MATCH function returns an array of n elements. Each value in the array is the MATCH function result for the nth element. For the example string, the array will look like this:

If every element in the delimited string is unique, this array would be filled by consecutive numbers from 1 to 13. It can be easily seen which elements do not fit that pattern. In order to calculate this, the array from the MATCH function must be set equal to the unique array, or:

which affords an array of Boolean values:

Use of the N function converts this array into ones and zeros, and the SUM function returns the desired result.

Well, that was the easy part. :)

The hard part of this formula is to convert a delimited string into an array of each element in the delimited string. This was accomplished by using the following formula:

To give credit where credit is due, the core of this formula was created by Rick Rothstein, see:

The truly amazing function of this formula is that it converts a delimited string into an array! I’m not going to go into an explanation here of how this formula works, since it is explained at the provided link. The original formula

was designed to return the nth element from a delimited string, but in this case all of the elements are returned in an array by replacing n with `ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))`

, which in this example returns `{1;2;3;4;5;6;7;8;9;10;11;12;13}`

. This is also the array used for RowArray in the simplified example.

So, this formula works great in Excel, but how could it be used in PowerPivot? For those using Excel 2013, and assuming that your column of delimited strings resides within a table in your DataModel, you can use a DAX query to bring the column into Excel, add the formula demonstrated here to the Excel table, and link the table back into the DataModel. A comprehensive example of this can be found at:

http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

So, for situations where solutions may not be possible in PowerPivot (or just incredibly complex), don’t forget about the power of Excel formulas.

BTW, an offshoot of the creation of this formula is another array formula that sums (or averages, whatever) the values in a delimited string:

A solution to this problem has been pursued in the Excel community for many years, so I am happy to present this formula here.

M SimmsVery slick formulas. However, if you build this out as a UDF using VBA, it’s much simpler.

Just call Chip Pearson’s Quicksort routine, compare the buckets and ignore the dupes via iterator, done.

For the Sum or Average, just do a Split, and call the Worksheet functions SUM or AVERAGE.

I did something very similar recently to create a RankUnique function since Excel’s Rank produces duplicates.

J GluckAnother way is to split the string into a range of cells, then apply a UDF from Erlandsen Consulting which treats the range as a collection, viz, “UniqueItem()”

David HagerM Simms, that’s the same story I heard back in 1994 when I first started creating complex Excel formulas.

I went on to make extremely complex functions with the xlm language and later with VBA, but I continued to

appreciate the challenge of solving a problem with a native Excel formula.

M SimmsIt would be interesting to see which approach performs better.

MSFT has done a lot to optimize formula calculations in the past 2 releases.

Rick Rothstein (MVP - Excel)@M Simms,

Here is another method that seems to work which does not involve sorting…

Function UniqueCount(ByVal S As String) As Long

Dim X As Long, Parts() As String

S = “@” & Replace(S, “,”, “@,@”) & “@”

Parts = Split(S, “,”)

For X = 0 To UBound(Parts)

If UBound(Split(S, Parts(X))) > 0 Then

UniqueCount = UniqueCount + 1

S = Replace(S, Parts(X), “,”)

End If

Next

End Function

Rick Rothstein (MVP - Excel)Just repeating my code, but trying something to see if I can format it better (might work or it might not work)…

Function UniqueCount(ByVal S As String) As Long

Dim X As Long, Parts() As String

S = “@” & Replace(S, “,”, “@,@”) & “@”

Parts = Split(S, “,”)

For X = 0 To UBound(Parts)

If UBound(Split(S, Parts(X))) > 0 Then

UniqueCount = UniqueCount + 1

S = Replace(S, Parts(X), “,”)

End If

Next

End Function

Jeff WeirCool! Here’s another formula approach:

Much longer than yours: 462 characters, as opposed to your 299.

Jeff WeirThis is such a great challenge, that I’ve posted it in the ‘Formula Challenges’ section at Chandoo’s forum. Will be interesting to see what other approaches are tried.

This particular challenge is posted at http://chandoo.org/forums/topic/formula-challenge-016-unique-items-in-a-delimited-string and there’s a whole bunch of other very tricky challenges listed under http://chandoo.org/forums/forum/excel-challenges if anyone is interested in testing their formula mettle.

Rick Rothstein (MVP - Excel)This array-entered formula is slightly shorter than yours (274 characters versus your 299) and appears to work correctly in Excel 2007 and above (the nesting levels are too large for earlier versions), which is a limitation of both your formula and Jeff’s as well…

=SUM(1*ISERROR(-SEARCH(TRIM(MID(SUBSTITUTE(A1,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999)),TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A1,” “,”@”)),”,”,REPT(” “,999)),(ROW(INDIRECT(“1:”&LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))-1)*999)))))

Rick Rothstein (MVP - Excel)Follow up to my last message

==================================

I note that all the quote marks in my formula will need to be replaced with proper quote marks if you copy the formula directly from my message and paste it (anywhere).

Jeff… what code tags did you use to get that scrolling display field which retained the proper quote marks?

Rick Rothstein (MVP - Excel)***BUG ALERT**

Do not use my previously posted formula as it will improperly count item like “aaaa,a,aaaa,a”. The following modification to my formula seems to correct the problem, but it also balloons the character count for the formula to 329. One positive my formula has over David’s formula, though, is that it returns 0 if A1 is empty whereas David’s returns 1. Anyway, for what its worth, here is the formula…

=SUM(1*ISERROR(-SEARCH(“,”&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,” “,”|”),”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))*999-998,999))&”,”,”,”&SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A1,” “,”|”)),”,”,REPT(” “,999)),(ROW(INDIRECT(“1:”&LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))-1)*999)),” “,”,”)&”,”)))

Jeff WeirHi Rick. I used the word CODE surrounded by lesser than and greater than symbols, with a backslash in the 2nd tag.

Jeff WeirHi Rick. Can you elaborate further the limitation that you allude to above re David and my formula?

Jeff WeirHere’s my revised method for converting a delimited string into an array:

At 181 characters, it’s still longer than David’s 113 character method.

This gets my formula down to 413 characters. Still much longer than David’s 299 one.

Jeff WeirWhoops, forgot to post my entire formula. And now there is a wall of my ugly mug pulling a funny face staring down at y’all.

Rick Rothstein (MVP - Excel)Two things… First, if A1 is blank, your formula returns a #VALUE! error and David’s returns 1. Second, all of our formulas fail to work in XL2003 (and I presume earlier). I believe the problem stems from a limitation in XL2003 of seven levels of nesting; see…

http://office.microsoft.com/en-us/excel-help/about-nesting-functions-within-functions-HP005198357.aspx

for details; XL2007 and above can handle up to 64 levels of nesting.

snb@Rick

sn = Split(Replace("@" & S & "@", ",", "@,@"), ",")

Do

sn = Filter(sn, sn(0), False)

UniqueCount = UniqueCount + 1

Loop Until UBound(sn) = -1

End Function

Dick KusleikaI used to have a little blurb about how to post code, but it appears to be gone. I wonder what happened to that. Well, it looks like this

<code>formula goes here</code>

<code lang=”vb”>vba goes here</code>

I can put <code lang=”vb” inline=”true”>code inline</code> too.

Rick Rothstein (MVP - Excel)@Dick – Thanks for the code tags (hope I remember them).

@snb – Clever use of False for the third argument to the Filter function (I really liked it).

snbIn comparison to the complexity of the Excel formula I prefer VBA:

sn = Split(c00, ",")

With CreateObject("scripting.dictionary")

For j = 0 To UBound(sn)

c01 = .Item(sn(j))

Next

F_unique_snb = .Count

End With

End Function

Dick KusleikaFrom http://msdn.microsoft.com/en-us/library/84k9x471%28v=vs.84%29.aspx

“Remarks

If key is not found when changing an item, a new key is created with the specified newitem. If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty”

Why did they design it that way? So there would never be an error?

Elias195 characters long!

......

=SUM(N(MATCH(

TRIM(MID(SUBSTITUTE(A1&REPT("",8^5),",",REPT(" ",999)),ROW($1:$999)*999-998,999)),

TRIM(MID(SUBSTITUTE(A1&REPT("",8^5),",",REPT(" ",999)),ROW($1:$999)*999-998,999)),

0)=ROW($1:$999)))-1

......

......

Regards

snbIndeed, you are avoiding any errors if you stick to work with keys only.

It performs pretty well to generate a list of unique keys.

Jeff WeirRick, your formula is pure genius.

Unless I’m missing something, you can ditch SUBSTITUTE(A1,” “,”|”) for just A1, which will make your array-generation portion identical to Davids. And you can also ditch the minus sign in front of the SEARCH.

Also, if this were a challenge to come up wit the shortest formula length, you could replace SEARCH with FIND and also replace this:

ROW(INDIRECT(“1:”&LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))

…with this:

ROW(OFFSET(A1,,,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””))+1))

Not that those changes make the resulting formula any better than your existing masterpiece. Just shorter for the sake of it. Down to 283 characters in fact.

For readers following along at home, then given data in A1 like this:

aa,c,d,aa,bbb,c,d,e,f,g,h,I,jjjjjjjj,aa

…you first split the string into two arrays:

Array 1:

Array 2:

…where array2 is just a list of incrementally concatenated elements of the string with a placeholder at the front (which offsets arary2 by one position compared to array1).

That offsetting of array2 this means that for any given element

nin array1, array2(n) containseverythingin the original stringup to that point. Or put another way, array2(n) = CONCATENATE(“,,” , array1(1) , array 1(2) , … , array1(n -1) )So when we search for array1(n) within array2(n), then if there’s a match, the thing we are looking for has obviously already occurred earlier in the string.

Pure genius.

Here’s how that looks graphically (assuming WordPress doesn’t mangle things):

Result array1 array2

#VALUE! ,aa, ,,

#VALUE! ,c, ,aa,

#VALUE! ,d, ,aa,c,

1 ,aa, ,aa,c,d,

#VALUE! ,bbb, ,aa,c,d,aa,

4 ,c, ,aa,c,d,aa,bbb,

6 ,d, ,aa,c,d,aa,bbb,c,

#VALUE! ,e, ,aa,c,d,aa,bbb,c,d,

#VALUE! ,f, ,aa,c,d,aa,bbb,c,d,e,

#VALUE! ,g, ,aa,c,d,aa,bbb,c,d,e,f,

#VALUE! ,h, ,aa,c,d,aa,bbb,c,d,e,f,g,

#VALUE! ,I, ,aa,c,d,aa,bbb,c,d,e,f,g,h,

#VALUE! ,jjjjjjjj, ,aa,c,d,aa,bbb,c,d,e,f,g,h,I,

1 ,aa, ,aa,c,d,aa,bbb,c,d,e,f,g,h,I,jjjjjjjj,

Jeff WeirDamn, I forgot the code tags on my 2nd array. Dick, would you kindly add code tags to Array2:

And also, is there a way to paste tablular data here, so that it shows in columns?

Doug JenkinsI was about to suggest a udf using the scripting dictionary, but I see snb has beaten me to it, and probably a good bit shorter than mine would have been.

Nice work.

Dick KusleikaYou can use html in the comments box, so table tags for tabular data. I recommend joinrange with the htmltable argument.

M Simmssnb – Now that is

slickand elegant. Hardly any code at all.snbYou could reduce the code to:

With CreateObject("scripting.dictionary")

For each it in Split(c00, ",")

c01 = .Item(it)

Next

F_unique_snb = .Count

End With

End Function

Doug JenkinsI think we to add a trim to the scripting dictionary version:

c01 = .Item(Trim(it))

Pingback: Select and sum data using the scripting dictionary | Newton Excel Bach, not (just) an Excel Blog

LoriNice formula! There is a slight limitation because the maximum string length is harcoded to 999, which restricts the number of delimited values to around 66 in my tests. To work around this I would suggest replacing 999 by LEN(A1) and 998 by (LEN(A1)-1). An alternative that also seems to work and is shorter (175 chars) – but probably no clearer :)

Agree with others that udfs are probably a better option here although maybe a bit slower than the suggested formulas for the sample string given in the post. In some basic optimisation tests, i found that the scripting dictionary udf could be made around twice as fast if references were included and would be quicker still using a collection in place of dictionary with “on error resume next” making it faster than the worksheet formulas as well as non-volatile. Would be nice to do a more detailed speed comparison given the time…

LoriTrying above formula again:

snbto digress somewhat more:

to return the list of unique items (not only the number of unique items):

With CreateObject("scripting.dictionary")

For Each it In Split(c00, ",")

c01 = .Item(it)

Next

F_uniquelist_snb = Join(.keys, ",")

End With

End Function

To return a sorted list of unique items:

With CreateObject("System.Collections.arraylist")

For Each it In Split(c00, ",")

If Not .contains(it) Then .Add it

Next

.Sort

F_uniquesortedlist_snb = Join(.toarray, ",")

End With

End Function

Stephen CrumpAnother formula approach. For readability, I have used S for the separator e.g. “,”

{=COUNT(1/SIGN(FIND(S&MID(A1,FIND(S,S&A1,ROW(1:999)),FIND(S,A1&S,ROW(1:999))-FIND(S,S&A1,ROW(1:999)))&S,S&A1&S)=ROW(1:999)))}

For example, for the string: a,b,a,c

Step 1: S&MID(A1,FIND(S,S&A1,ROW(1:999)),FIND(S,A1&S,ROW(1:999))-FIND(S,S&A1,ROW(1:999)))&S splits out the four comma-wrapped components “,a,” | “,b,” | “,a,” and “,c,”

Step 2: Find each of these components in the comma-wrapped string “,a,b,a,c,” and count 1 if they’re found in their actual position. The second “,a,” component is located at position 5, but found at position 1, therefore must be a duplicate.

It appears to work for all well-defined strings, including spaces, e.g. it returns 4 for “a,, ,b” which I’d argue is correct. But if A1 is blank, it returns 1.

Pingback: Some Power Query/M Examples - SQL Server - SQL Server - Toad World

LoriStephen, that’s nice too. It’s pretty much the same as mine which uses “SUMPRODUCT(- -(” instead of “COUNT(1/SIGN(” and so gives an error for blanks. The only other differences are using a variable length array (which does make it many times faster to recalc the test string at the expense of being volatile) and MMULT to reduce function calls.

Jeff WeirThat’s awesome, Lori. I’m yet to fully digest it, but noted that you can make it shorter still by replacing any instances of this:

ROW(INDIRECT(“1:”&LEN(A1)))

…with this:

ROW(OFFSET(A1,,,LEN(A1)))

…leaving you this 169 character puppy:

Jeff WeirStephen, that’s a fantastic approach. Note that you can make it slightly shorter by removing the S from A1&S and instead adding 1 to the final result. Plus you don’t need the SIGN:

What’s cool about it is that it can be completely dynamic in terms of string length:

Jeff WeirIf we combine Stephen’s approach with Loris, then we get this masterpiece that handles any length text string up to the cell limit, in 179 characters:

Bam!

Pingback: Arrays vs Collections vs Dictionary Objects (and Dictionary help) | Newton Excel Bach, not (just) an Excel Blog