cant figure out how to count unique

[mikedela]

I cannot get a formula or funciton to work right, but anyone have a suggestion for taking cell A! with this data:
JE,RK,SH,SB,NC,GH,CD,DL,KFS,BI,EE,GC,TV,HL,JD,DI,LD,MM,BA,CK

and then taking cell A2 with this data
QP,DP,BL

I need to take those two cells and add the initils (notice one has 3 initials and make sure it is always 23 total with no duplicates. This will always change. for example, something a few from A1 will move to A2, but then A1 will be less, with A2 having more. the end result has to still be 23 with no duplicates. Ideas?

Bascially there will always be only these entries spread around cells with no other additions.

JE,RK,SH,SB,NC,GH,CD,DL,KFS,BI,EE,GC,TV,HL,JD,DI,LD,MM,BA,CK,QP,DP,BL

 

[jasonb75]

Something like

=AND(COUNTIF($A$1:$A$2,"*"&{"JE","RK","SH","SB","NC","GH","CD","DL","KFS","BI","EE","GC","TV","HL","JD","DI","LD","MM","BA","CK","QP","DP","BL"}&"*")=1)

This will return TRUE if all 23 specified initials appear once.

 

[mikedela]

thanks....how would I make it Cell B3 and B10 now?

=AND(COUNTIF($B$3:$B$10,"*"&{"JE","RK","SH","SB","NC","GH","CD","DL","KFS","BI","EE","GC","TV","HL","JD","DI","LD","MM","BA","CK","QP","DP","BL"}&"*")=1)

 

[jasonb75]

It only works with adjacent cells, if you use it with B3:B10 then the content of the other cells in the range would also be evaluated and the results could be incorrect.

Try

=SUM(IF(ISNUMBER(SEARCH({"JE","RK","SH","SB","NC","GH","CD","DL","KFS","BI","EE","GC","TV","HL","JD","DI","LD","MM","BA","CK","QP","DP","BL"},B3&B10)),1))

 

[mikedela]

sweet...that worked great. Now if I take it a step further and want to start moving my initials around (these are physicians), how could I check that B5 through B19 totals 23 as I start deleting some initials and move them to other cells within the B5 - B19 range? I tried this and it doesnt work:

=SUM(IF(ISNUMBER(SEARCH({"JE","RK","SH","SB","NC","GH","CD","DL","KFS","BI","EE","GC","TV","HL","JD","DI","LD","MM","BA","CK","QP","DP","BL"},B5:B19)),1))

this returns a "0".

 

[jasonb75]

Try array confirming it with Shift Ctrl Enter.

 

[mikedela]

that works perfect!! thanks so much. Can I ask for more help?

Is there a way to do the same check and flag somehow if there is a duplicate anywhere? I can set it to conditional if greater than 23 or less than 23, but what if I accidentally put in a physicians initials twice somewhere in that range and left out a physician? Ideas on that?

 

[jasonb75]

Actually looking back, you could use an earlier formula

=AND(COUNTIF($B$3:$B$10,"*"&{"JE","RK","SH","SB","NC","GH","CD","DL","KFS","BI","EE","GC","TV","HL","JD","DI","LD","MM","BA","CK","QP","DP","BL"}&"*")=1)

This makes sure that each entry appears once in the range specified, when I said questioned the suitability of that one, I was under the impression that it should look at only B3 and B10, not the other cells between as well.

For any cell in a specified range you can use the method above, for specified non-adjacent cells you need to use the format I suggested in post #4, with the specified cells concatenated, i.e. B3&B5&B10

Hope that makes sense.