Deb Tilton
asked on
Excel sort for duplicate records
I have a big Excel worksheet with Name & Contact information entered. It probably has close to 2,500 records.
I want to transfer all the sheet tabs into a new worksheet without certain columns and without their column row headings.
After I do all that I am going to sort for duplicate records.
I know in all this there are Company Names listed more than once in more than 1 category.
I want to create inquiry cards to send to these companies.
So far, I have transfered the tab information into a whole new workbook of its own.
I would like someone to tell me how they would have transferred these tabs to a new sheet (because it seems I probably did it the long, hard way because I had some issues I had to fix)
and I really want someone to walk me through setting up the sort process to get to the duplicate records.
I want to transfer all the sheet tabs into a new worksheet without certain columns and without their column row headings.
After I do all that I am going to sort for duplicate records.
I know in all this there are Company Names listed more than once in more than 1 category.
I want to create inquiry cards to send to these companies.
So far, I have transfered the tab information into a whole new workbook of its own.
I would like someone to tell me how they would have transferred these tabs to a new sheet (because it seems I probably did it the long, hard way because I had some issues I had to fix)
and I really want someone to walk me through setting up the sort process to get to the duplicate records.
the easy way would be to put the information into Access and use the Find Duplicates Query wizard ...
It can be done in Excel too, of course -- with a lot a lot of code
It can be done in Excel too, of course -- with a lot a lot of code
ASKER
I really don't have time to mess with Access. I think it would take too long.
I am ready to sort and filter to find the duplicate entries and I have to do this on Monday.
I am ready to sort and filter to find the duplicate entries and I have to do this on Monday.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
which version of Excel are you using?
The easiest way to move all tabs into a single sheet is to use VBA - I can provide a sample workbook if you want to try it
for filtering in versions above 2013 (possibly 2010) you can try this:
a. select the column with the 'most obvious' potential duplicate (company name?)
b. in the home ribbon under 'conditional formats' select 'highlight duplicates' and choose a colour
c. select the next 'most obvious' column
d. do the conditional format with a different colour
Now turn on filtering for all columns and in the drop down you can sort and filter by colour and then look across the data set to see where all the colours match and any rows which have all colours the same can be deleted.
there are faster automated ways but its usually best to have a human interpretation unless you can live with the risk of some going missing or remaining as duplicates
The easiest way to move all tabs into a single sheet is to use VBA - I can provide a sample workbook if you want to try it
for filtering in versions above 2013 (possibly 2010) you can try this:
a. select the column with the 'most obvious' potential duplicate (company name?)
b. in the home ribbon under 'conditional formats' select 'highlight duplicates' and choose a colour
c. select the next 'most obvious' column
d. do the conditional format with a different colour
Now turn on filtering for all columns and in the drop down you can sort and filter by colour and then look across the data set to see where all the colours match and any rows which have all colours the same can be deleted.
there are faster automated ways but its usually best to have a human interpretation unless you can live with the risk of some going missing or remaining as duplicates
Glad to be of help!
ASKER
just emphasizing I need to do the sorting for duplicates process on Monday