Solved

Excel sort for duplicate records

Posted on 2016-08-27
7
79 Views
Last Modified: 2016-09-01
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.
0
Comment
Question by:Deb Tilton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 

Author Comment

by:Deb Tilton
ID: 41773094
Note:  I do not have access to the spreadsheet myself if that is needed

just emphasizing I need to do the sorting for duplicates process on Monday
0
 
LVL 20
ID: 41773097
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
0
 

Author Comment

by:Deb Tilton
ID: 41773425
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.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 20

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41773729
I assume you are not familiar with Access -- it would be relatively quick to do this.
In Excel, if column headings are not in the first row and data starts in the next row then create a range name for the data starting with the row with column names -- highlight data and column labels (just ONE row) then click in the address box and type any name you want starting with a letter with no spaces or special characters (ie: ImportData). Save and close the Excel workbook

  1. Make a blank database -- browse to a path where you can find it again and name it logically
  2. From the ribbon, choose External Data and click on Excel
  3. Choose the sheet or range name
  4. Follow the wizard steps

Access will name the new table whatever is the sheet or range name.
If any column heading contains illegal characters for a field name, Access will call it Field1, Field2, etc.

  1. Click on  the Create ribbon
  2. Choose Query Wizard
  3. Choose Find Duplicates Query Wizard
  4. click Next
  5. Choose the table to look at
  6. click Next
  7. Choose the field(s) to compare for duplicates
  8. click Next
  9. click >> to add rest of fields
  10. click Next
  11. change query name if desired and click Finish
0
 
LVL 2

Accepted Solution

by:
psteff earned 250 total points
ID: 41774735
If you are looking for exact duplicates in a particular column, there are a few ways you can find them.  Probably the easiest is the following.
If the data is in column A starting with row 1, you could put this formula into row one of the next empty column to the right of your data.
=countif($A$1:$A1,A1)
Copy this formula down as far as your data.  This will count the number of occurrences of your data.  The first occurrence of duplicate data will have a count of one, the rest of the occurrences will have counts greater than one.  Therefore, if you do a copy and paste values on the count column, you can then sort and delete all the rows that are greater than one.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 41778322
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
0
 
LVL 2

Expert Comment

by:psteff
ID: 41779878
Glad to be of help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question