@Prince Instead of using dictionary or array a single line can do it
If No Header ActiveSheet.Range("$A$1:$A$23").RemoveDuplicates Columns:=1, Header:=xlNo If Header ActiveSheet.Range("$A$1:$A$23").RemoveDuplicates Columns:=1, Header:=xlYes Regards, Lalit Mohan On Thursday, 10 January 2013 12:46:14 UTC+5:30, Prince wrote: > > If yes then please check this one > > I have programmed below code just cut and paste this and run > > Public Sub removeDuplicateId() > > Dim vardata As Variant > Dim varRes As Variant > Dim lngRow As Long > Dim objDic As Object > > > Set objDic = CreateObject("Scripting.Dictionary") > > With ThisWorkbook.Worksheets("Sheet1") > vardata = > Application.WorksheetFunction.Transpose(Intersect(.Range("A1").CurrentRegion, > .Range("A1").CurrentRegion.Offset(1))) > For lngRow = LBound(vardata) To UBound(vardata) > If objDic.exists(vardata(lngRow)) = False Then > objDic.Add vardata(lngRow), vardata(lngRow) > End If > Next > varRes = Application.WorksheetFunction.Transpose(objDic.items) > .Range("B2").Resize(UBound(varRes), 1) = varRes > End With > > End Sub > > > Note: > 1: Changed the name of sheet as i have used sheet1 you can replace the > sheet name according your sheet name > 2: I have Assumed all the data in A coloumn from A2 and A1 as Header text > 3: It will provide result in column B at B2 > > Regards > Prince > > On Thursday, January 10, 2013 12:44:31 PM UTC+5:30, Prince wrote: >> >> so do u need any macro ? >> regards >> Prince >> >> >> >> On Thursday, January 10, 2013 12:30:42 PM UTC+5:30, kasper wrote: >>> >>> Hi >>> >>> Thanks for reply,I know about duplicate removeal but I am serching for a >>> permanent formula . >>> >>> Regards >>> Rajesh >>> >>> On Tuesday, January 1, 2013 9:23:20 PM UTC+5:30, Prince wrote: >>>> >>>> Hi Kasper, >>>> >>>> Dear instead of giving solution i just want to give the idea required >>>> to accomplish your task.just follow the below mentioned steps and it help >>>> you to do it from your own. >>>> >>>> 1:Select the columns of employee name . >>>> 2:click : Data Tab >>>> 3: Spot the option : Remove duplicate >>>> 4: it will ask extend your selection or continue with selection >>>> 5:Choose Continue with selection >>>> 6:And thats it. >>>> >>>> Note:if you want this unique data to be displayed in some other >>>> column then select your whole data and copy it some other place then >>>> follow >>>> above steps and paste the result at the required place >>>> >>>> Regards >>>> Prince >>>> >>>> >>>> >>>> On Tuesday, January 1, 2013 6:30:24 PM UTC+5:30, kasper wrote: >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Hi Experts, >>>>> >>>>> Please refer the attached file & tell me >>>>> How to select employee names from the column "B" and display in Column >>>>> "D" after removing the duplicate. >>>>> >>>>> Regards >>>>> Rajesh >>>>> >>>>> -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.