How to Count Number of Unique Occurrences within an Alphanumeric String

[dwgnome]

Looking for a way to count the number of unique values. Note that some cells have multiple entries. Need to ignore the text and dates and just count the number of “numbers” even though they themselves are in a text field. So for the example below, the number of unique values is 12. Note: need to ignore letters after the “numbers” as that is referring to the same value just amended - in this case the "A".</SPAN>


[TABLE="class: grid, width: 201"]
<TBODY>[TR]
[TD]FOT A52281
[/TD]
[/TR]
[TR]
[TD]POL A46168
[/TD]
[/TR]
[TR]
[TD]KUY A46159 (3/2&3/3/04)</SPAN>
[/TD]
[/TR]
[TR]
[TD]WEV A46159 (3/2&3/3/04) </SPAN>
IOV A46170</SPAN> (1/31/2005)</SPAN>
[/TD]
[/TR]
[TR]
[TD]WEV A46159
IOV A46170 (1/31/2005)</SPAN>
[/TD]
[/TR]
[TR]
[TD]NOV A46159
[/TD]
[/TR]
[TR]
[TD]TIM A46175H
[/TD]
[/TR]
[TR]
[TD]SAM A46175
[/TD]
[/TR]
[TR]
[TD]DTO A46174
[/TD]
[/TR]
[TR]
[TD]UAL A48207
[/TD]
[/TR]
[TR]
[TD]PIR A46070
[/TD]
[/TR]
[TR]
[TD]RTE A46070
[/TD]
[/TR]
[TR]
[TD]TX A46072
[/TD]
[/TR]
[TR]
[TD]Y A52283
NV A52284
[/TD]
[/TR]
[TR]
[TD]PQW A52283
O A52284
[/TD]
[/TR]
[TR]
[TD]ZRR A52283
ZRR A52284
[/TD]
[/TR]
[TR]
[TD]FOT A52281A
[/TD]
[/TR]
[TR]
[TD]FOT A52281A
[/TD]
[/TR]
[TR]
[TD]TIM A52283
TIM A52284
[/TD]
[/TR]
</TBODY>[/TABLE]

I tried using:</SPAN>
=COUNTIF(D10:D28,"*A*") but results in 19</SPAN>
=SUM(IF(COUNTIF(D9:D28,D9:D28)=0,"",1/COUNTIF(D9:D28,D9:D28))) results in 18</SPAN>
=SUM(IF(FREQUENCY(MATCH(D10:D28,D10:D28,0),MATCH(D10:D28,D10:D28,0))>0,1)) Also results in 18</SPAN>
and</SPAN>
=SUMPRODUCT((D10:D28<>"")*(1/COUNTIF(D10:D28,D10:D28&""))) results in 18</SPAN>

While there is always an “A” before the number, it may not show up. Also, there are blank rows in my real data, so I need to ignore those. If this helps, the number of digits are always 5 (i.e., 52283).</SPAN>


Excel 2003.</SPAN>

 

[dwgnome]

Bump:

The formulas again hopefully easier to read:</SPAN>

=COUNTIF(D10:D28,"*A*")

but results in 19</SPAN>

=SUM(IF(COUNTIF(D9:D28,D9:D28)=0,"",1/COUNTIF(D9:D28,D9:D28)))

results in 18</SPAN>

=SUM(IF(FREQUENCY(MATCH(D10:D28,D10:D28,0),MATCH(D10:D28,D10:D28,0))>0,1))

Also results in 18</SPAN>
and</SPAN>

=SUMPRODUCT((D10:D28<>"")*(1/COUNTIF(D10:D28,D10:D28&"")))

results in 18</SPAN>

While there is an “A” before the number, it may not always show up. Also, there are blank rows in my real data, so I need to ignore those. If this helps, the number of digits are always 5 (i.e., 52283).</SPAN>

Any help is appreciated.


Excel 2003</SPAN>

 

[AhoyNC]

If you can use a helper column then see if this will work for you.
It assumes that the 5 numbers will always be the first 5 numbers in the text string.

Also, I only see 11 unique items not 12.

The formula in B1 must be entered with CTRL-SHIFT-ENTER (copy this formula down to your last row of data).

Excel Workbook
	A	B	C
1	FOT A52281	52281	11
2	POL A46168	46168
3	KUY A46159 (3/2&3/3/04)	46159
4	WEV A46159 (3/2&3/3/04)	46159
5	IOV A46170 (1/31/2005)	46170
6	WEV A46159	46159
7	IOV A46170 (1/31/2005)	46170
8	NOV A46159	46159
9	TIM A46175H	46175
10	SAM A46175	46175
11	DTO A46174	46174
12	UAL A48207	48207
13	PIR A46070	46070
14	RTE A46070	46070
15	TX A46072	46072
16	Y A52283	52283
17	NV A52284	52284
18	PQW A52283	52283
19	O A52284	52284
20	ZRR A52283	52283
21	ZRR A52284	52284
22	FOT A52281A	52281
23	FOT A52281A	52281
24	TIM A52283	52283
25	TIM A52284	52284
Sheet

 

[dwgnome]

Thanks AhoyNC. Very clever. I think you are right there are only 11 unique numbers. My original data had 12 and I think I missed one when I set up this example.

I think you are on the right track, but when I tried your suggestion, it only counted 9. This may be that there are actually two sets of 5 digits in one cell and the helper formula only extracts the first one, not both. Secondly if there is blank row, the result is #NA and then the SUMPRODUCT portion also results in #NA. However, when I adjusted the SUMPRODUCT(($B$1:$B$25<>"") range to the left one column that worked even though it still gives the incorrect result.

 

[AhoyNC]

I think the formula below will take care of the blank cells, but I'm not sure how to handle two sets of 5 in the same cell.

Excel Workbook
	A	B	C
1	FOT A52281	52281	11
2	POL A46168	46168
Sheet

 

[István Hirsch]

Give these formulas a try, enter them with Shift-Ctrl-Enter:

Into B1:

=IFERROR(MID(A1,FIND(" A",A1)+2,5),"")

Into C1:

=IFERROR(MID(A1,FIND(" A",A1,7)+2,5),"")

Into D1:

=SUMPRODUCT(($B$1:$C$25<>"")*(1/COUNTIF($B$1:$C$25,$B$1:$C$25&"")))

 

[dwgnome]

Thanks Ahoy. Yes you are right, your solution still does not address the two or more sets per cell. Your revised formula may work, but I get an error preventing me from entering the formula. The error says cannot enter formula due to too many nested levels allowed by the current file format.

Thanks Istvan, your solution worked. It is a bit more work than I would normally would want as I would have to anticipate extra helper columns if more than two sets of 5 digits were present. I also used ISERROR as I have Excel 2003.

Thanks everyone for their help.