I created a macro to load the Unique Numbers,
then I created a Change Event to run the macro whenever a change takes place
in the target area:
-----------------------------------------------------------------------------
Option Explicit
Public Dict_Val
Sub Load_Data()
Dim R, C, Rcnt, dKeys, tVal
Set Dict_Val = CreateObject("Scripting.Dictionary")
Dict_Val.RemoveAll
For C = 2 To 9
Rcnt = Application.WorksheetFunction.CountA(Range(Cells(3, C),
Cells(1000, C)))
For R = 3 To 1000
If (Cells(R, C).Value & "X" = "X") Then
If (R > Rcnt) Then Exit For
Else
If (Not Dict_Val.exists(Cells(R, C).Value)) Then
Dict_Val.Add Cells(R, C).Value, 1
End If
End If
Next R
Next C
Application.ScreenUpdating = False
Range("K3:K1000").ClearContents
dKeys = Dict_Val.keys
For R = 0 To UBound(dKeys)
Cells(R + 3, "K") = dKeys(R)
Next R
Range("K2:K1000").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("K3:K502") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("K2:K1000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
Range("K3").Select
End Sub
-------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Targ
If (Target.Count > 1) Then
For Each Targ In Target
If (Targ.Column >= 2) And (Targ.Column <= 9) Then Load_Data
Next Targ
Else
If (Target.Column >= 2) And (Target.Column <= 9) Then Load_Data
End If
End Sub
-------------------------------------------------------------------------------
Paul
________________________________
From: Sundarvelan N <nsund...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Mon, January 10, 2011 10:53:16 PM
Subject: $$Excel-Macros$$ Unique numbers using Macro OR Foumula
Hello Friends,
Can anybody help me to achieve my task.
In the attached file i need to get the unique numbers using formula or MACRO.
When numbers entered in the left table i need to get the unique numbers in the
unique column. Please help me friends
--
ThanksN.Sundarvelan
9600160150--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
