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>

 

[shawnhet]

You could try posting the source data for the Vlookups. Right now, there is no particularily easy way of doing what you are asking for, so getting what you are asking for is tricky at best.

Cheers,

 

[dwgnome]

The formula used in all the columns is the following:

=IF(ISBLANK(INDEX(TableAll,ROW()-7,MATCH(B$8,INDEX(TableAll,1,0),0))),"",INDEX(TableAll,ROW()-7,MATCH(B$8,INDEX(TableAll,1,0),0)))

From my experience, the resultant values are what is needed to be acted on, independent of how they got there. In this case for row 8, the unit IDs 295 - 297, 300 - 302, 304 - 314, 326, 333 were entered in TableAll just the way it appears in the Sheet 1 column in this example.

 

[dwgnome]

Ideas are welcomed.

 

[BenMiller]

It looks like everyone has the same opinion. Excel works best with your data when all the data is actually entered into cells, not just your starting and ending points. Is there any way you could do that?

 

[dwgnome]

Like I said, I cannot change how the data is setup. I just need to know if it is possible to isolate the numbers and do a summary count as presented in my original post. I am hoping one or more of this Board's moderators can provide further guidance (Aladin, T.Valko, DonkeyOte perhaps?).

 

[T. Valko]

Like I said, I cannot change how the data is setup. I just need to know if it is possible to isolate the numbers and do a summary count as presented in my original post. I am hoping one or more of this Board's moderators can provide further guidance (Aladin, T.Valko, DonkeyOte perhaps?).

This is not possible with a formula.

You might be able to do it with a VBA UDF but I can't help you with that.

 

[dwgnome]

Well, then hopefully someone with VBA experience can assist. Thank you for responding.

 

[DonkeyOte]

There will be more elegant methods but as a starting point:

Excel Workbook
	A	B	C
1	Unit IDs
2
3	330
4	330
5	24
6	332
7	332
8	295 - 297, 300 - 302, 304 - 314, 326, 333
9	316 - 323
10	16 to 23
11
12	38
Sheet1

Function UniqueIDs(rngStr As Range) As Variant
    Dim vUID, vTemp1, vTemp2
    Dim cCol As Collection
    On Error GoTo Handler
    Set cCol = New Collection
    vUID = rngStr.Value
    For lngU = LBound(vUID, 1) To UBound(vUID, 1) Step 1
        vTemp1 = Split(Replace(Replace(vUID(lngU, 1), "to", "-"), "-", ":"), ",")
        For lngT = LBound(vTemp1) To UBound(vTemp1) Step 1
            vTemp2 = Split(vTemp1(lngT), ":")
            For lngItem = Trim(vTemp2(LBound(vTemp2))) To Trim(vTemp2(UBound(vTemp2))) Step 1
                On Error Resume Next
                cCol.Add lngItem, CStr(lngItem)
                On Error GoTo Handler
            Next lngItem
        Next lngT
    Next lngU
    UniqueIDs = cCol.Count
    Set cCol = Nothing
    Exit Function
Handler:
    UniqueIDs = "Error"
End Function

edit: removed Evaluate - unnecessary

 

[dwgnome]

Thank you DonkeyOte, that worked a treat.

Is there a way to modify the code to still provide a count when an error is encountered instead of just "error". That is to continue to add/count the "valid" cells using "-", "to" and "," if someone placed "through" or forgot to enter a "-" and just had the two number in the cell. The result would state 36 or whatever the number is and after it state "check ranged entries for valid symbols".

 

[DonkeyOte]

I'd be inclined to add just a handler around the numeric iteration - obviously some fairly significant assumptions were made regards data types etc...

Function UniqueIDs(rngStr As Range) As Variant
    Dim vUID, vTemp1, vTemp2
    Dim cCol As Collection
    Dim boolError As Boolean
    On Error GoTo Handler
    Set cCol = New Collection
    vUID = rngStr.Value
    For lngU = LBound(vUID, 1) To UBound(vUID, 1) Step 1
        vTemp1 = Split(Replace(Replace(vUID(lngU, 1), "to", "-"), "-", ":"), ",")
        For lngT = LBound(vTemp1) To UBound(vTemp1) Step 1
            vTemp2 = Split(vTemp1(lngT), ":")
            On Error GoTo Invalid
            For lngItem = Trim(vTemp2(LBound(vTemp2))) To Trim(vTemp2(UBound(vTemp2))) Step 1
                If IsNumeric(lngItem) Then
                    On Error Resume Next
                    cCol.Add lngItem, CStr(lngItem)
                    On Error GoTo Handler
                End If
            Next lngItem
NextValue:
            On Error GoTo Handler
        Next lngT
    Next lngU
    UniqueIDs = cCol.Count & IIf(boolError, " (with errors - check range for invalid symbols)", "")
    Set cCol = Nothing
    Exit Function
Invalid:
    boolError = True
    Resume NextValue
    Exit Function
Handler:
    UniqueIDs = "Error"
    Exit Function
End Function