Count Unique IDs

[dwgnome]

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 107px"><COL style="WIDTH: 84px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 29px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; COLOR: #000080; FONT-WEIGHT: bold">Unit IDs</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99; COLOR: #000080; FONT-WEIGHT: bold">Actual No</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">330</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">1</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">330</TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">24</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">1</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">332</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">1</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">332</TD><TD style="FONT-WEIGHT: bold"></TD></TR><TR style="HEIGHT: 50px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">295 - 297, 300 - 302, 304 - 314, 326, 333</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">19</TD></TR><TR style="HEIGHT: 30px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">316 - 323</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">8</TD></TR><TR style="HEIGHT: 30px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">16 to 23</TD><TD style="TEXT-ALIGN: center; COLOR: #808080">8</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Total Units: </TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">38</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B12</TD><TD>=SUM(B2:B10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Need a one cell formula that can count the number of unique unit IDs. For multiple IDs in a cell, the dash or the word "to" are meant to be inclusive (assumes continuous numbering) while the commas separate the series. I prefer not to have a helper column like shown here.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[dwgnome]

Bump.

 

[dwgnome]

Can someone get me started or point me in the right direction?

 

[BenMiller]

Explain your question.

 

[dwgnome]

I am wanting to add the cumulative number of units in the column excluding any duplicates. For example in row 9, 316 - 323 means 316, 317, 318, 319, 320, 321, 322, 323. So there are eight units represented in that cell. The formula will need to isolate the two numbers and and then subtract them 323 minus 316 then add 1 to account for all units or, perform some other way to account for eight units. In other cells, there is just the one ID so the formula will need to just identify the one unit. All of these will then be summed.

 

[dwgnome]

Hopefully with my expanded explanation, someone can help me on this.

 

[BenMiller]

This would have been a LOT easier if all the actual data had been entered, and in some sort of normalized format... Let me think.

 

[dwgnome]

Would appreciate if someone could help me solve my post.

 

[BenMiller]

Easiest way is probably to first do a CTRL+H and change every "to" to "-". Then do text to columns and split them on the dashes. Then find a UDF online (google around, shouldn't be hard to find) to list every number between the starting and finishing point. Then pile up all the numbers you have and remove duplicates.
Sorry, this is the best I've got!

 

[dwgnome]

BenMiller's assistance/solution while appreciated does not work for me as the unit ID's are a result of lookup formula and there are other columns not shown in this example that I have that can't be moved or changed.

I still prefer a formula solution to solve the original question. Any other takers?