I have a sheet that has general mailing format (name, adress, zip, etc) which i want to mail merge, problem is there are 3000 lines and a lot are duplicates, is there an easy way to eliminate duplicates based on 1 field (company name or adress) before i merge it so i don’t have to do it manually?
free sex for teh winnah
I would use Access if I was you. If you don’t have it, e-mail me the file and I will do a unique on that field.
Are the rest of the fields in those records different? Like little things that technically aren’t the same, but mean the same thing? Like Rd. and Road.?
Not sure how to do it in EXCEL, I’m an ACCESS programmer.
i’m not sure if you mean use one field to get rid of duplicates or remove duplicates if only 1 field is the same.
here is how you remove duplicates based on 2 columns:
make an identifier column, ie column A = ident
lets say column B = company name
lets say column C = company address
then for cell A2 do: =B2&C2
and drag that value all the way down (or double click the bottom-right corner of that cell)
if you are just basing on one column:
sort by that column (lets call it column A)
regardless… next:
sort on the identifier column (column A)
now you need another column, call it “dupe”, lets call this column D
for cell D2 do: =if(A2=A1,“duplicate”,"")
and drag that value all the way down (or double click the bottom-right corner of that cell)
then select all, copy, paste special values
set up an autofilter on the first row
filter on column D = “duplicate”
select all rows by clicking the row# on the left side and selecting down with the keyboard (or click the first number and press shift-ctrl-downarrow)
right click on the column identifier numbers and choose delete.
lol… thats so much harder than Access… but it works.
:tup:
yeah the find duplicates query in access is nice, but i think doing mindless things like this in excel is a good way to feel productive and waste more time to keep from doing other work
plus i’ve had to become an Excel guru because lots of salespeople are scared of access files.
one time i had to build a fairly complicated database with search and sort capability… in excel. it came out really sweet and i learned it was possible. just took a LOT of programming
I don’t even want to program in Excel… much better programs to use… that are farily user friendly… but they are skurred … I know the feeling, believe me.