Finding unique numbers in a dataset

S

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
 
Sort by date Sort by votes
segran said:
@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.
Click to expand...
You can use try this code
Code: 
Sub uniquez()
Dim d As Object, e As Variant
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For Each e In Range("A1:AX4524").Value
    d(e) = 1
Next
Range("AZ1").Resize(d.Count) = Application.Transpose(d.keys)
End Sub
Note that if there's a lot of uniques, from memory >2^16 or so, the transpose function may not work.

However, if so there's several other ways, although maybe requiring code a couple of lines longer.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Oops, apart from mine being not nearly so compact as mirabeau's, I got my last row digits all mixed up. :oops:
 
Upvote 0
Hi Peter,

I ran it, and I got a compile error : on;y comments may appear, End function, or End Property.

Please help.
 
Upvote 0
Apologies, but the error was on the last TRUE to the code - Application.ScreenUpdating = True
 
Upvote 0
@Mirabeau - Brilliant. Thank You. It worked great :)

@ Peter_SSs - Thank you. Please do tell me how to correct your code, so I can understand yours as well.

Thank you :)
 
Upvote 0
segran said:
Hi Peter,

I ran it, and I got a compile error : on;y comments may appear, End function, or End Property.

Please help.
Click to expand...
segran said:
Apologies, but the error was on the last TRUE to the code - Application.ScreenUpdating = True
Click to expand...
Are you sure you are talking about my code? I don't have any reference to ScreenUpdating in my code.

The correction to my code would just be to correct the row number in this line
Rich (BB code): 
a = Range("A1:AX4524").Value
 
Upvote 0
Hi Peter_SSs

Yes it was your code I used. I tried your code 1st, and that was the error I received.

I am uncertain of the problem.

Thank you
 
Upvote 0
segran said:
Hi Peter_SSs

Yes it was your code I used. I tried your code 1st, and that was the error I received.

I am uncertain of the problem.

Thank you
Click to expand...

segran said:
Apologies, but the error was on the last TRUE to the code - Application.ScreenUpdating = True
Click to expand...
Perhaps I'm misunderstanding you but you appear to say the error related to Application.ScreenUpdating.

Could you clarify what the actual error was when you ran my code?

Edit: Sorry, I've just seen your last post - no error. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA to create list of unique values matching a condition, drawn from a large dataset
Replies
6
Views
1K
dermie_72
D
K
  • Solved
Use 'Unique', 'Filter' and 'If' statements.
Replies
7
Views
227
Fluff
Fluff
T
  • Question Question
Assistance with Dividing Accommodation Costs by FTE
Replies
3
Views
95
Vogateer
V
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
204
Lux Aeterna
L
L
  • Question Question
Auto numbering to start at another character than the first character
Replies
3
Views
339
lmaritz
L

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

Share this page

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