Unique Values Help

[Misterchipper]

I have 2 columns of data as listed below. Very simply, what I want to do is determine the number of UNIQUE IDS found within each categorization. For example, within 10Neurology>=1, there are 3 IDS, but only 1 unique. Essentially, I want the 1 to be the result. Anyway to do this, besides importing into Access?

<TABLE style="WIDTH: 295pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=393><COLGROUP><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 122pt; mso-width-source: userset; mso-width-alt: 5961" width=163><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 173pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=230>Categorization</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 122pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=163>ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>10NEUROLOGY>=1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1865552</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>10NEUROLOGY>=1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1865552</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>10ANESTHESIOLOGY>=1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>861369</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>10ANESTHESIOLOGY>=1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>861369</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>10NEUROLOGY>=1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1865552</TD></TR></TBODY></TABLE>

 

[Zakkaroo]

One option would be to use an Advanced Filter:

Highlight all your data

Click:
Data >> Filter >> Advanced Filter

Select: Copy to Another Location
Select: Unique Records Only

In the Copy To Field, select another sheet or just somewhere off to the side of your original data.

You'll now have a list of all the unique records. At that point you could use a pivot table, or the COUNTIF function to determine how many of each there are.

 

[hiker95]

Misterchipper,

If your data looks like this:

Excel Workbook
	A	B	C	D	E
1	Categorization	ID
2	10NEUROLOGY>=1	1865552
3	10NEUROLOGY>=1	1865552
4	10ANESTHESIOLOGY>=1	861369
5	10ANESTHESIOLOGY>=1	861369
6	10NEUROLOGY>=1	1865552
7
Sheet1

Highlite columns A and B

Click on Data, Filter, AdvancedFilter…

Click on Copy to another location

To the right of the Copy to box, click on the icon

Click in cell D1

Press the Enter key

Click on Unique records only

And then click on the OK button.


And you will get this:

Excel Workbook
	A	B	C	D	E
1	Categorization	ID		Categorization	ID
2	10NEUROLOGY>=1	1865552		10NEUROLOGY>=1	1865552
3	10NEUROLOGY>=1	1865552		10ANESTHESIOLOGY>=1	861369
4	10ANESTHESIOLOGY>=1	861369
5	10ANESTHESIOLOGY>=1	861369
6	10NEUROLOGY>=1	1865552
7
Sheet1

 

[MickG]

Try this:-
The Results will be "Catogories" in Column "C" and Numbers Unique I/D's in "D".

[COLOR="Navy"]Sub[/COLOR] MG24Mar28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
    ReDim Ray(1 To Rng.Count, 1 To 2)
        ReDim nRay(1 To Rng.Count, 1 To Columns.Count)
            [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.value, Array(1, Dn.Offset(, 1), n, 0)
            Ray(n, 1) = Dn.value: Ray(n, 2) = 1
    [COLOR="Navy"]Else[/COLOR]
        Fd = False
        Q = .Item(Dn.value)
        Q(3) = Q(3) + 1
        nRay(Q(2), Q(3)) = Q(1)
                [COLOR="Navy"]For[/COLOR] Ac = 1 To Q(3)
                    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 1) = nRay(Q(2), Ac) [COLOR="Navy"]Then[/COLOR]
                        Fd = True
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] Fd = False [COLOR="Navy"]Then[/COLOR] Q(0) = Q(0) + 1
        .Item(Dn.value) = Q
        Ray(Q(2), 2) = Q(0)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[hiker95]

Misterchipper,


If you are looking for a macro solution.


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E
1	Categorization	ID
2	10NEUROLOGY>=1	1865552
3	10NEUROLOGY>=1	1865552
4	10ANESTHESIOLOGY>=1	861369
5	10ANESTHESIOLOGY>=1	861369
6	10NEUROLOGY>=1	1865552
7
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Categorization	ID		Categorization	ID
2	10NEUROLOGY>=1	1865552		10NEUROLOGY>=1	1865552
3	10NEUROLOGY>=1	1865552		10ANESTHESIOLOGY>=1	861369
4	10ANESTHESIOLOGY>=1	861369
5	10ANESTHESIOLOGY>=1	861369
6	10NEUROLOGY>=1	1865552
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetUnique()
' hiker95, 3/24/2011
' http://www.mrexcel.com/forum/showthread.php?t=538504
Columns("D:E").ClearContents
Columns("A:B").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True
Columns("D:E").AutoFit
End Sub

Then run the GetUnique macro.