• Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
  • Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
Quick Support
Book Demo

Learn how to identify duplicate rows in your data

By admin on August 29, 2018April 19, 2019

In a previous tip, we explained how you can easily remove duplicate rows from your data using the Remove Duplicates function.  You can find it here.  At times though, you may just want to highlight duplicate rows without actually deleting them.  In this tip, we show you how you can do this using an Excel formula.

1. First, you need to make sure that your data is sorted. Assuming it starts in cell A1 and has headings, select cell A2 and then click Sort A to Z on the Data tab on the menu ribbon.

2. Next, enter a new column in front of column A by right clicking on the label for column A and selecting Insert.

3. Now, in cell A2, we’re going to make use of an IF and AND function to identify duplicates. Enter the following:

=IF(AND(B2=B3,C2=C3,D2=D3,E2=E3,F2=F3), “Duplicate”, “Unique”)

Note that the formula is checking to see whether the value in cell B2 is equal to the value in cell B3 and whether the value in cell C2 is equal to the value in cell C3 and whether the value in cell D2 is equal to the value in cell D3, etc.  If all combinations are equal, then it implies that the row is a duplicate and the function returns the text “Duplicate”.  If not, then “Unique” is returned.

Also keep in mind that your data will likely vary in the number of columns it has and you will need to do the cell comparison for each column that you have.  Furthermore, you may regard a duplicate as consisting of a subset of columns.  For example, in the above data, I might think of a row as being duplicated only if the Customer Name and Product Name are the same.  In that case you would only do the cell comparison for those rows.

4. Now, all you need to do is use the Excel fill handle and copy the formula down to the rows below it. You will notice that any duplicates are identified.

Through the power of Excel functions, you can easily identify duplicate rows which can make working with your data more efficient.

The post Learn how to identify duplicate rows in your data appeared first on Sage Intelligence.

Source: Excel on Steroids

Post navigation

How to create a linked dashboard (or landing page) for your Excel workbook
Serialising 7.3 and Later

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Why Working In Excel Works
  • Alchemex Welcomes Velixo 6 For MYOB Advanced
  • Welcome to Velixo Reports Version 4.2
  • Part 3 of 3: Learn how to create a Management Pack in Sage 50cloud Intelligence
  • Part 3 of 3: Create and customize reports easily with the Financial Report Designer

Search

Contact Us

Phone: +61 2 9890 2339
Email: sales@alchemex.com.au
Address: PO Box 212, Deaken West, Canberra, Australia, 2600

Products

  • Intelligence Reporting
  • Velixo Reports

Services

  • Consulting
  • Training
  • Development

About

  • About Us
  • Partners
  • Blog

Support

  • New Ticket
  • Community (Coming Soon)
  • Knowledgebase

Phone: +61 2 9890 2339
Sales: sales@alchemex.com.au
Support: support@alchemex.com.au

Facebook-f Linkedin

© All rights reserved Alchemex Pty Ltd 2020

Made with ❤ by Unfazed Ltd