Removing duplicates in excel

If you don't find any appropriate forum thread to post your question, then post on this thread by creating a new topic.
Minuca Elena
Junior Member
Posts: 1
Joined: Mon Jul 25, 2016 2:02 pm

Removing duplicates in excel

Postby Minuca Elena » Mon Jul 25, 2016 2:29 pm

Hello,

Do you know if there is an option to remove duplicate rows in Excel? I'm using excel '97.

Thank you,
Minuca

Atish Ranjan
Administrator
Posts: 288
Joined: Sat Jul 23, 2016 12:12 pm
Location: India
Contact:

Re: Removing duplicates in excel

Postby Atish Ranjan » Mon Jul 25, 2016 2:58 pm

Minuca Elena wrote:Hello,

Do you know if there is an option to remove duplicate rows in Excel? I'm using excel '97.

Thank you,
Minuca



Hello Minuca,

thanks for asking the question.

I haven't used excel '97, I have been using the later versions.

As per the excel version I am using:

First sort your list by going to Data, and then select the cells, and under Data, you will find "remove duplicate" option. Just click on that, and your work will be done.

Hope someone else on this forum may have used, then he/she can help.

However, I tried finding the solution for it and found something here => http://windowssecrets.com/forums/showthread.php/32024-Removing-Duplicate-Rows-(Excel-97)-(Excel-97)

You can put a VBA script as macro, and run it! and it will be done.
Atish Ranjan
Administrator

sklohar
Junior Member
Posts: 6
Joined: Sat Jul 30, 2016 4:07 pm
Contact:

Re: Removing duplicates in excel

Postby sklohar » Sun Jul 31, 2016 1:44 pm

There is menu option provided in excel by microsoft which is already mention in above comment.

I just want to share the shortcut way to identify and remove the duplicate entry in excel. [I'm using MS Office 2007]

1. To recongize duplicate entry:
On your keyboard press following button one by one:
ALT-H-L-H-D-ENTER

2. To delete duplicate entry:
ALT-A-M-TAB-TAB-ENTER
Have a nice day, ahead! :)
- SK Lohar

Atish Ranjan
Administrator
Posts: 288
Joined: Sat Jul 23, 2016 12:12 pm
Location: India
Contact:

Re: Removing duplicates in excel

Postby Atish Ranjan » Sun Jul 31, 2016 1:49 pm

sklohar wrote:There is menu option provided in excel by microsoft which is already mention in above comment.

I just want to share the shortcut way to identify and remove the duplicate entry in excel. [I'm using MS Office 2007]

1. To recongize duplicate entry:
On your keyboard press following button one by one:
ALT-H-L-H-D-ENTER

2. To delete duplicate entry:
ALT-A-M-TAB-TAB-ENTER


Thanks for taking part in the discussion Shiv! But, it seems Minuca wants the solution for 97-2003 version of Excel.

Will this trick work for that version?
Atish Ranjan
Administrator

sklohar
Junior Member
Posts: 6
Joined: Sat Jul 30, 2016 4:07 pm
Contact:

Re: Removing duplicates in excel

Postby sklohar » Sun Jul 31, 2016 2:33 pm

Atish Ranjan wrote:
Thanks for taking part in the discussion Shiv! But, it seems Minuca wants the solution for 97-2003 version of Excel.

Will this trick work for that version?



MS Office 97-2003 is slightly older version. I think there is no harm in upgrading to later version. As later version provide more feature which are very effective and reduces most of our work.

Many new feature are added in later version. Even though of Minuca want to use older version then she has to go with Data menu but I am not sure whether removing duplicates option is available directly in that menu.
Have a nice day, ahead! :)
- SK Lohar

Atish Ranjan
Administrator
Posts: 288
Joined: Sat Jul 23, 2016 12:12 pm
Location: India
Contact:

Re: Removing duplicates in excel

Postby Atish Ranjan » Sun Jul 31, 2016 2:45 pm

sklohar wrote:
Atish Ranjan wrote:
Thanks for taking part in the discussion Shiv! But, it seems Minuca wants the solution for 97-2003 version of Excel.

Will this trick work for that version?



MS Office 97-2003 is slightly older version. I think there is no harm in upgrading to later version. As later version provide more feature which are very effective and reduces most of our work....


yes, that is very correct. But sometimes, people are with older version and they need solution in that version only because upgrading may take time for them. I have already suggested her to upgrade as new versions are quite better.

Thanks.
Atish Ranjan
Administrator

meghrajsingh
Junior Member
Posts: 1
Joined: Fri Aug 12, 2016 11:21 am

Re: Removing duplicates in excel

Postby meghrajsingh » Fri Aug 12, 2016 1:33 pm

When we don't have readymade shortcuts for the excel requirements, then excel macro gives us the power to code the things for the outcome we wish to see.

Below is the code that will suite your requirement and serves the purpose well.

Code: Select all

total_rows = Sheets("Sheet1").UsedRange.Rows.Count
total_unique_number = 0
'

For Count1 = 2 To total_rows
    number_matched = False
    For Count2 = 2 To total_rows
        If Sheets("Sheet1").Range("A" & Count1).Value = Sheets("Sheet1").Range("A" & Count2).Value And Count1 <> Count2 Then
            number_matched = True
            Exit For
        End If
    Next Count2

total_unique_number = Range("B" & Rows.Count).End(xlUp).Row
    If number_matched = False Then
        Sheets("Sheet1").Range("B" & total_unique_number + 1).Value = Sheets("Sheet1").Range("A" & Count1).Value
   
    End If

Next Count1


Below are the image snapshots, before and after the code is run:
Before
1.png
1.png (6.36 KiB) Viewed 1688 times


After
1.png
1.png (6.36 KiB) Viewed 1688 times
Attachments
2.png
2.png (7.64 KiB) Viewed 1688 times


Return to “Miscellaneous”

Who is online

Users browsing this forum: No registered users and 2 guests