Finding unique numbers in a dataset

[segran]

Hi,

I have a huge dataset with repeating but unique identifiers.
For example, an identifiers A23333, will apear in the dataset 20 times.

I want to find and list this unique identifiers in a single column, with any repeats.

I do not know what the unique identifiers are hence my request.

Please assist with a simple macro or formula.

My data set has 4524 rows and ends in column AX.

Thank you

 

[Desu Nota from Columbus]

Sub FindUnique()
Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = Range("A" & Rows.Count).End(xlUp).Row

Range("AZ2:AZ" & lrow).Formula = "=SUMPRODUCT(--($A$2:$A$4524=A2))"

For r = lrow To 2 Step -1

    If Range("AZ" & r).Value = 1 Then
        Range("AY" & r).Value = Range("A" & r).Value
    End If

Next r


Application.ScreenUpdating = True

        
End Sub

Try this.

This macro will search A2:A4524 for any unique records (records that only appear once in column A) and it will list them in column AY.

 

[Desu Nota from Columbus]

This one might be better suited:

Sub FindUnique()
Dim r As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = Range("A" & Rows.Count).End(xlUp).Row

Range("AZ2:AZ" & lrow).Formula = "=SUMPRODUCT(--($A$2:$A$4524=A2))"

For r = lrow To 2 Step -1

    If Range("AZ" & r).Value = 1 Then
        Range("AY" & r).Value = Range("A" & r).Value
    End If

Next r


For r = lrow To 1 Step -1

If Range("AY" & r).Value = "" Then
    Range("AY" & r).Delete (xlUp)
End If

Next r


Application.ScreenUpdating = True

        
End Sub

Creates a list in AY without any blanks!

 

[segran]

Thank you very much.
How do I use it though.
Copy and paste and run?

 

[segran]

Hi,

I have used the code. And it counts whats in the dataset using whats in column one as the criteria.

What I wanted was, you rearrange the matrix into a single column, but deleting the repeats in the data set. can this be done.

Please HELP.

Thank you

 

[Peter_SSs]

What version of Excel are you using?

 

[mirabeau]

Do you mean that you want every cell in the range(A1:AX45240 ) to be checked for duplicates, and the unique cells to be listed in a single column?

If this is so, do you want the column to replace the original range, or should the column be placed somewhere else?

 

[SydneyGeek]

This will take a while...

Build a pivot table based on the dataset. Add the first field twice, once to the Row layout and once to the Values. Make sure that the summary is Count.
Copy the data and paste values to another sheet.

Repeat with the other fields, placing the new summaries below the first.

When finished you will have a listing of the unique values and counts for each column. One more pivot table, using the 2 fields from this output, will give you the final result.

Denis

 

[segran]

Do you mean that you want every cell in the range(A1:AX45240 ) to be checked for duplicates, and the unique cells to be listed in a single column?

If this is so, do you want the column to replace the original range, or should the column be placed somewhere else?

@Mirabeau - Yes to question one. You can list the column to be pasted in another column, say AY or so. Dont replace the original range.

@ Peter_SSs - I am using Excel 2007.

Thank you for your assistance.

 

[Peter_SSs]

See if this does what you want.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Unique_List()<br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    a = Range("A1:AX2454").Value<br>    LR = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>    LC = <SPAN style="color:#00007F">UBound</SPAN>(a, 2)<br>    <SPAN style="color:#00007F">With</SPAN> CreateObject("Scripting.Dictionary")<br>        .CompareMode = vbTextCompare<br>        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>            <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> LC<br>                s = a(i, j)<br>                <SPAN style="color:#00007F">If</SPAN> Len(s) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> .Exists(s) <SPAN style="color:#00007F">Then</SPAN><br>                        k = k + 1<br>                        .Add s, s<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> j<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        Range("AZ1").Resize(k).Value = Application.Transpose(Array(.Keys))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>