Search and delete identical files. How to Find Same Values ​​in an Excel Column

Good day.

Statistics are an inexorable thing - for many users hard drives Sometimes there are dozens of copies of the same file (for example, pictures, or music track). Each of these copies, of course, takes up space on the hard drive. And if your disk is already “filled” to capacity, then there can be quite a lot of such copies!

Cleaning duplicate files manually is not a rewarding thing, which is why I want to collect in this article programs for finding and removing duplicate files (even those that differ in both file format and size from each other - and this is quite difficult task!). So…

List of programs for finding duplicates

1. Universal (for any files)

Carry out a search identical files by their size (checksums).

Under universal programs, I understand, those that are suitable for searching and deleting duplicates of any type of file: music, movies, pictures, etc. (below in the article, “its own” more accurate utilities will be given for each type). Most of them work according to the same type: they simply compare file sizes (and their checksum), if among all the files there are identical ones in this characteristic - they show you!

Those. thanks to them you can quickly find it on disk full copies(i.e. one to one) files. By the way, I’ll also note that these utilities work faster than those that are specialized for a specific type of file (for example, image search).

DupKiller

I put this program in first place for a number of reasons:

  • supports simply huge number diverse different formats, which she can search;
  • high speed;
  • free and with Russian language support;
  • very flexible settings for searching for duplicates (search by name, size, type, date, content (limited)).

Duplicate Finder

This utility, in addition to searching for copies, also sorts them as you please (which is very convenient when there are an incredible number of copies!). Also add byte-by-byte comparison and reconciliation to the search capabilities checksums, deleting files from zero size(And empty folders Same). In general, this program does a pretty good job of finding duplicates (both quickly and efficiently!).

Those users who are new to English will feel a little uncomfortable: Russian is not in the program (maybe it will be added later).

Glary Utilities

In general, this is not one utility, but a whole collection: it will help you delete “junk” files, set optimal settings on Windows, defrag and clean hard drive etc. Including this collection there is a utility for searching for duplicates. It works relatively well, which is why I will recommend this collection (as one of the most convenient and universal - as they say, for all occasions!) once again on the pages of the site.

2. Programs for finding duplicate music

These utilities will be useful to all music lovers who have a decent collection of music accumulated on their disk. I'm drawing pretty typical situation: download various music collections (100 best songs October, November, etc.), some of the compositions are repeated in them. It is not surprising that, having accumulated 100 GB of music (for example), 10-20 GB can be copies. Moreover, if the size of these files in different collections were the same, then they could be deleted by the first category of programs (see above in the article), but since this is not the case, these duplicates cannot be found by anyone except your “hearing” And special utilities (which are presented below).

M usic Duplicate Remover

The result of the utility.

This program differs from others, first of all, in its fast search. It searches for duplicate tracks by their ID3 tags and sound. Those. she will listen to the composition for you, remember it, and then compare it with others (thus doing a huge amount of work!).

The screenshot above shows the result of her work. She will present her found copies to you in the form of a small tablet, in which a percentage similarity figure will be assigned to each track. In general, quite convenient!

A

Found duplicate MP3 files...

This utility is similar to the one above, but it has one undoubted advantage: the presence of a convenient wizard who will guide you step by step! Those. a person who launches this program for the first time will easily figure out where to click and what to do.

For example, in my 5000 tracks in a couple of hours, I managed to find and delete several hundred copies. An example of how the utility works is shown in the screenshot above.

3. To search for copies of pictures, images

If you analyze the popularity of certain files, then the pictures will probably not lag behind the music (and for some users they will surpass them!). It’s hard to imagine working on a PC (and other devices) without pictures! But searching for images with the same image on them is a rather difficult (and long) task. And, I must admit, there are relatively few programs of this kind...

ImageDupeless

A relatively small utility with fairly good performance in finding and eliminating duplicate images. The program scans all the images in the folder and then compares them with each other. As a result, you will see a list of pictures that are similar to each other and you will be able to make a conclusion about which one to keep and which to delete. It is very useful, sometimes, to thin out your photo archives.

ImageDupeless example

By the way, here is a small example of a personal test:

  • experimental files: 8997 files in 95 directories, 785MB (archive of pictures on a flash drive (USB 2.0) - gif and jpg formats)
  • gallery occupied: 71.4MB
  • creation time: 26 min. 54 sec.
  • time for comparison and output of results: 6 min. 31 sec.
  • result: 961 similar image in 219 groups.

Image Comparer

I have already mentioned this program on the pages of the site. It is also a small program, but with quite good algorithms scanning pictures. There is a step-by-step wizard that starts when you first open the utility, which will guide you through all the “thorns” of first setting up the program to search for duplicates.

By the way, just below is a screenshot of the utility’s operation: in the reports you can even view small details, where the pictures are slightly different. In general, it’s convenient!

4. To search for duplicates of films and videos

Well, the last popular file type that I would like to dwell on is video (films, videos, etc.). If once before, having a 30-50 GB disk, I knew in which folder where and what movie takes up how much (and they were all at odds), then, for example, now (when disks became 2000-3000 GB or more) - they often occur the same videos and films, but in different quality (which can take up quite a lot of space on your hard drive).

Most users (yes, in general, me too 🙂) don’t need this state of affairs: it just takes up space on the hard drive. Thanks to a couple of utilities below, you can clear your disk of identical videos...

In today's Excel files duplicates are found everywhere. For example, when you create a composite table from other tables, you may find duplicate values ​​in it, or in a file with shared access entered the same data two different user, which led to doubling, etc. Duplicates can occur in one column, in multiple columns, or even in the entire worksheet. IN Microsoft Excel Several tools have been implemented for searching, highlighting and, if necessary, removing duplicate values. Below are the basic techniques for identifying duplicates in Excel.

1. Removing duplicate values ​​in Excel (2007+)

Let's say you have a three-column table with identical entries and you need to get rid of them. Select the area of ​​the table in which you want to remove duplicate values. You can select one or more columns, or the entire table. Go to the tab Data to the group Working with data, click on the button Remove duplicates.

If each table column has a header, set the marker My data contains headers. We also place markers opposite those columns in which we need to search for duplicates.

Click OK, the dialog box will be closed and rows containing duplicates will be deleted.

This function is designed to delete records that completely duplicate rows in the table. If you haven't selected all columns to identify duplicates, rows with duplicate values ​​will also be removed.

2. Using an advanced filter to remove duplicates

Select any cell in the table, go to the tab Data to the group Sorting and Filter, click on the button Additionally.

Advanced filter, the switch must be set to position copy the result to another location, in the field Original range indicate the range in which the table is located in the field Place result in range specify the top left cell of the future filtered table and set the marker Only unique values. Click OK.

In the place specified for placing the results of the advanced filter, another table will be created, but with data filtered by unique values.

3. Highlight duplicate values ​​using conditional formatting in Excel (2007+)

Select the table in which you want to detect duplicate values. Go to the tab Home to the group Styles, choose Conditional Formatting-> Rules for highlighting cells -> Repeating values.

In the dialog box that appears Duplicate values you need to select a format for highlighting duplicates. I have the default color set to light red fill and dark red text color. Please note in in this case Excel will not compare the entire table row for uniqueness, but only the column cell, so if you have duplicate values ​​in only one column, Excel will format them too. In the example, you can see how Excel has filled some cells in the third column with names, although the entire row of this table cell is unique.

4. Using Pivot Tables to Determine Repeating Values

Let's use a table with three columns already familiar to us and add a fourth, called Counter, and fill it with units (1). Select the entire table and go to the tab Insert to the group Tables, click on the button Pivot table.

Create a pivot table. In the field Line names place the first three columns in the field Values We place a column with a counter. In the created pivot table, records with a value greater than one will be duplicates, the value itself will indicate the number of duplicate values. For greater clarity, you can sort the table by column Counter to group duplicates.

A common question is how to find and remove duplicates in Excel. Let's assume you downloaded a monthly report from your accounting system, but in the end you need to understand which counterparties generally interacted with the company during this period - leave the list of counterparties without repetition. How to select unique values?

Is it possible to remove doubled, tripled, etc. values ​​in Excel across multiple columns?

It is possible, and very simple. For this there is special function. Pre-select the range where you want to remove duplicates. On the ribbon, go to Data - Remove duplicates (see the picture at the beginning of the article).

Selecting the first column

It is important to understand that if you select only the first column, then all data in the unselected columns will be deleted if it is not unique.

Very convenient!

2. How to select all duplicates in Excel?

Have you already heard about? Yes, this is where it will help! Select the column in which you want to mark duplicates, select from the menu Main - Conditional Formatting - Rules for highlighting cells - Duplicate values...

In the Repeating values ​​window that opens, select which cells to select (unique or repeating), as well as the selection format, either from those suggested, or create a Custom format. The preset format will be red fill and red text.

Click OK if you do not want to change the formatting. Now all the data for the selected conditions will be colored.

I note that the tool is applied only to the selected one (!) column.

By the way, if you need to see unique ones, then in the window on the left select - unique.

3. Unique values ​​using pivot tables

To be honest, I once did not suspect the existence of the ability to “remove duplicates” and used pivot tables. How did I do it? Select the table in which you need to find unique values ​​- Insert -

Greetings, dear reader! Today I will show you a program that searches for identical files on the computer. The program not only finds copies of files, but also, at the user’s request, immediately deletes them. Very convenient in this regard. And there may be so many copies of files that you won’t even suspect it. They just might be in different folders and even on different drives. You may use some of them all the time, but you may have forgotten about their copies.

For example, they downloaded a picture from the Internet, used it for their own purposes and forgot about it. After some time, you needed this picture, but you were too lazy to look for it on the computer. It's easier to find it on the Internet. Download again and get a duplicate file that already exists on your computer.

The same can happen with music files. You downloaded it into different folders and think that you have it in a single copy. Many PC users make one mistake. When you click on a file with the left mouse button and drag it to another folder located on another disk, it is not moved, but copied. This means that the file remained on same place, and in new folder there was a copy of it on another disk.

It turns out that one file is superfluous and only takes up free space in the computer's memory.

Search for identical files

This program has flexible settings with which we can speed up the search.

Let's say we are looking only on one or two disks. We tick them and press the “Scan” button

But the program will find all files that have copies. But we don’t need this, because, for example, we only want to find images.

Search by file type

In this case, go to the “Files and Folders” tab. Check the file format boxes. Images come in different formats, but the program offers us only four: jpg, jpeg, gif, bmp. These are the most popular image formats that almost every user has.

The rest that are not in the list must be entered manually. Click the “Add” button. In the window that opens, enter required format pictures. For example from the program Photoshop-(*.PSD)

OK! We scan and get a bunch of copies to delete. Stop! But they can be systemic. So let's move on.

We scan only the necessary folders

Let's choose separate folders for scanning. The program will only check them. At the bottom of the program there is a setting “Searched folders” Check the item “ Only specified folders"With such parameters, you don’t have to select the disk in the “Disks” tab. Yes, and don't forget to check the box here " Include these folders even if the corresponding drive is not selected «

We scan and get the result. Once the scan is complete, DupKiller will switch to the “List” tab, where all found duplicate files will be shown.

The files, in our case, are pictures, sorted into groups. A group consists of two or more files. They are all the same, as they are copies of each other.

Which files should I delete?

Click on any of the files in the group, and you will see a thumbnail image in the preview window. Now just scroll the mouse wheel to move through the list and compare copies with each other.

All information about the file is visible on the program screen. And even if the image is not displayed in the preview window, we can compare files by name, size and type. The first column, called "Path", shows the location of the file.

Deleting identical files

We look at this data and select one file in each group. Now the marked files can be deleted by clicking on the “Delete” or “” button. You can also use the “Delete” key on the keyboard to delete.

If there are too many files to delete, then it is better to use automatic deletion files. In this case, you choose from which folder to delete identical files. How does this work? Highlight right key mouse one file in the group and click on the “Auto select” button

In the window that appears in the top block, the paths to folders in which there are similarities between the files will be displayed. The same folders are listed in the lower block, but are not checked. We need to select one of these folders in which the files should be deleted. Click “Ok”

There's another problem here. Each time you delete one of the copies, a confirmation window appears.
You're tired of confirming. Disable this notification by going to the “Delete” settings and uncheck the “ Ask for confirmation before deleting«

Well, that's it. I superficially showed you the principle of operation of the DupKiller program.

There is a desire to delve into additional settings « Search settings" And " Other settings«

And in my opinion, she does her job well.

Write in the comments how you like this program and how you clean disk space then unnecessary copies?

This is useful to know:


A program for finding duplicate files is most often needed by users who store on disk large number music, photos and documents.

And, although you can delete such extra copies manually, specialized applications can save a lot of time.

Especially if the files are located in different folders or in .

You can search for such duplicates using the universal software or designed for a specific data type.

In the first case, the search speed increases, in the second, the probability of finding all copies increases.

Content:

Universal Applications

Universal Applications to find copies, they mainly work on the principle of comparing file sizes.

And, since the probability of matching the number of bytes is different photos practically equal to zero, same values are considered a sign of a duplicate.

Sometimes the algorithm involves checking names - also important parameter for searching, especially since the same data in most cases also coincides in name.

The advantages of the programs are the ability to find files of any type with their help and the relatively high speed of operation. Disadvantage: lower detection accuracy.

So, for example, none of these utilities will consider the same file saved with different resolutions to be a duplicate.

1. DupKiller

And among its advantages we can note:

  • ease of setup;
  • setting multiple search criteria;
  • ability to ignore some files (with certain size or creation date, as well as system or hidden).

Important: If files with a size of zero are found, they do not have to be deleted. Sometimes this may be information created on another operating system (for example, Linux).

Rice. 4. Optimization program CCleaner systems can also search for duplicate files.

5.AllDup

Among the advantages of another program, AllDup, we can note support for any modern operating system Windows – from XP to 10.

At the same time, the search is also carried out inside hidden folders, and even in the archives.

Although the comparison of information by default occurs by file names, so it is advisable to change the settings immediately.

But during the search process, each duplicate found can be viewed without closing the application.

And if a copy is found, you can not only delete it, but also rename it or move it to another location.

TO additional benefits application applies and completely free work for any period of time.

In addition, the manufacturer also produces portable version in order to search for copies on those computers on which installation of third-party software is prohibited (for example, on a work PC).

Rice. 5. Search for files using the portable version of AllDup.

6. DupeGuru

One more useful application The one that searches for duplicates with any extension is DupeGuru.

Its only drawback is the lack of new versions for Windows (although updates for MacOS appear regularly).

However, even a relatively outdated utility copes well with its tasks when working in newer operating systems.

With its help, even system files are easily detected, and the menu is intuitive and in Russian.

Rice. 6. Detecting copies using the DupeGuru utility.

It is noteworthy that, in addition to the usual universal option, the manufacturing company has created a utility for searching for files of a certain type.

There is a separate version for images and another for music.

And, if necessary, clean your computer not only of documents and system files(which, by the way, need to be deleted very carefully - sometimes it’s even worth leaving an “extra” copy rather than disrupting the system’s functionality), it’s worth downloading these applications as well.

7. Duplicate Cleaner Free

Duplicate Cleaner Free, a utility for detecting copies of any file, has the following features:

  • filtering data by extension;
  • Russian interface language;
  • opportunity free use;
  • high speed work.

Its disadvantages include minor limitations when searching for images (for this it is recommended to purchase paid version) and not entirely accurate translation individual elements menu

However, due to its effectiveness and ease of use, the application enjoys some popularity.

Rice. 7. Find duplicates using the Duplicate Cleaner Free utility.

Finding duplicate audio files

If the duplicate search results do not satisfy the user, you can consider an option designed for certain files. For example, for accumulated on disk.

This need often arises when downloading several albums and collections of the same artist at once - often the same tracks end up in different folders.

They may have similar sizes and differ, by and large, only in names. Especially for this, there are utilities for searching for similar melodies.

8.Music Duplicate Remover

Among the features of the Music Duplicate Remover program are relatively quick search and good efficiency.

In fact, this application “listens” to the composition and compares it with other audio files.

At the same time, naturally, its operating time is longer than that of universal utilities.

However, the amount of data checked by the program is usually tens of times less, so the average scan duration rarely exceeds a couple of hours.

Rice. 8. Detection of copies of music and audio files by album.

9.Audio Comparer

At the same time, photo analysis also takes longer compared to searching for files of any extension, but the result is worth it.

Images are detected even when there are several duplicates of the same image on the disk, but with different resolutions and, accordingly, size.

In addition, to increase efficiency, files with any graphic extensions– from to.png.

Rice. 11. Search for pictures using another version of DupeGuru.

12. ImageDupeless

Moreover, it is distributed free of charge and has a Russian interface. And the manufacturer periodically releases updates to it, increasing the efficiency of image search.

Rice. 12. Stylish interface of the ImageDupeless application.

13. Image Comparer

The advantages of the Image Comparer application except simple interface, we can call the presence step-by-step wizard, which allows you to learn how to quickly and efficiently search for images.

This feature distinguishes the utility from most others, to work with which you will have to read help files that are not always translated correctly (and sometimes even provided only in English).

In fact, the application is another Audio version Comparer, and is also distributed under a “shareware” license - that is, for certain functions the user will have to pay.

Rice. 13. The Image Comparer app is a good way to find duplicate pictures.