addicted Posted January 27, 2009 Share Posted January 27, 2009 I've got a worksheet with 2000 names in Column A. The range is A1:A2000 The names repeat throughout the list. Can anyone tell me how to quickly delete all of the duplicate names and just leave the unique names? What's the formula to do that? I've tried things like =UNIQUE(A1:A2000) but that didn't work, help please! Link to comment Share on other sites More sharing options...
Mark The Homer Posted January 27, 2009 Share Posted January 27, 2009 Sounds to me like you're going to have to write yourself a macro. Maybe a data search or find function combined with some If then else statements. Have fun. Link to comment Share on other sites More sharing options...
Dictator Posted January 27, 2009 Share Posted January 27, 2009 you could do an If statement, find all the repeats then delete them. =IF(A3=A2,"Delete","Keep") Link to comment Share on other sites More sharing options...
jrockster21 Posted January 27, 2009 Share Posted January 27, 2009 I've got a worksheet with 2000 names in Column A. The range is A1:A2000 The names repeat throughout the list. Can anyone tell me how to quickly delete all of the duplicate names and just leave the unique names? What's the formula to do that? I've tried things like =UNIQUE(A1:A2000) but that didn't work, help please! The only thing I can think of is to sort the rows alphabetically, and manually delete the repeat entries. I don't think there is a built in function for what you want to do. Link to comment Share on other sites More sharing options...
Pwyl Posted January 27, 2009 Share Posted January 27, 2009 Step one: sort column A. Step two: put this formula in B:2 "=IF(A2=A1,1,0)" step three: drag the forumula down to the end of the range Step four: copy column B, "paste special => values" back on top of column B Step five: Sort column B descending Step six: delete all rows with a 1 in column B Link to comment Share on other sites More sharing options...
jrockster21 Posted January 27, 2009 Share Posted January 27, 2009 Sounds to me like you're going to have to write yourself a macro. Maybe a data search or find function combined with some If then else statements. Have fun. Yeah, you could do it in VB with a couple loops. Loop through the column twice - checking for repeat values. Meanwhile build an array of unique values and then print it out on a new worksheet. Link to comment Share on other sites More sharing options...
Dictator Posted January 27, 2009 Share Posted January 27, 2009 or you could do what me and Pwyl said and save a ton of time Link to comment Share on other sites More sharing options...
addicted Posted January 27, 2009 Author Share Posted January 27, 2009 Trying to figure out how to do what Pwyl said here but I'm stuck on stupid. Ok so rows sorted. I put this in cell B2 "=IF(A2=A1,1,0)", but cant figure out step three? When I drag over A1-A2000 nothing happens except the cell in B2 changes to the number 1 What did I do wrong? Link to comment Share on other sites More sharing options...
Pwyl Posted January 27, 2009 Share Posted January 27, 2009 You're looking to "fill" the formula down to the last row in B that has a corresponding value in A. Quickest way is to mouse-over the bottom right corner of the cell B2 until the mouse pointer changes to a small + sign, then double-click. Link to comment Share on other sites More sharing options...
addicted Posted January 27, 2009 Author Share Posted January 27, 2009 You rock Pwyl, that worked. Went from 2000 to 170. Thanks! Link to comment Share on other sites More sharing options...
da#1skinsfan Posted January 27, 2009 Share Posted January 27, 2009 what a glorious display of internet productivity. how did people get by before such social connectivity existed!?!?!?! Link to comment Share on other sites More sharing options...
addicted Posted January 27, 2009 Author Share Posted January 27, 2009 :notworthy what a glorious display of internet productivity.how did people get by before such social connectivity existed!?!?!?! Hahha :applause: Link to comment Share on other sites More sharing options...
Mufumonk Posted January 27, 2009 Share Posted January 27, 2009 There is a much easier way to do this. Highlight the column you want to use to filter for unique records. Under the Data tab go to Filter|Advanced Filter. Check the box that reads "Unique Records Only" and hit Ok. This will leave you with a listing of records minus duplicates. Keep in mind this doesn't delete the duplicates.....it's only a manifest of available names. Fill the last column with an "X" and Show All records under the Filter tab. Any record without an "X" in the last column is a duplicate and can be removed. Of course Access would make your life a lot easier. Edit: Of course Pwyl's method is easier for those of us familiar/comfortable with formulas. :OLS Link to comment Share on other sites More sharing options...
mjah Posted January 27, 2009 Share Posted January 27, 2009 what a glorious display of internet productivity.how did people get by before such social connectivity existed!?!?!?! Arcane manuals, "tips and tricks" paperbacks from the computer store, phone calls to geek friends, and good old fashioned elbow grease. And, compared with today, lots of abandoned efforts. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.