Finding unique numbers in a dataset

segran

Active Member
Joined
Aug 20, 2004
Messages
335
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Code:
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.
 
Last edited:
Upvote 0
This one might be better suited:

Code:
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top