Menu
homeBios

How to find identical files in a folder. How to find duplicate values ​​in Excel. The fastest programs for finding duplicate photos

After a long time, permanent use computer, on its disks, whatever one may say, accumulate large amounts of data, i.e. all sorts of photographs, videos, films, music, documents, etc. When data takes up a lot of space, this is normal, for example, I myself have more than 600 GB of necessary data, and for others it’s even more. But very often duplicate files take up too much space.

Such files can appear when, for example, you transfer them from somewhere to a new location on the disk, forgetting that you already have such files on this disk. And it’s okay if there are a lot of duplicates of all kinds of documents, but when there are a lot of duplicate photos, music and especially videos, then this, as a rule, will take up a lot of your disk space. I recently checked and found that duplicates are eating up about 100 GB. on the hard drive, which, in my opinion, is quite a lot :)

In this article, I will show you an easy way to find all duplicate files in Windows on your drives, so you can easily check them and quickly delete everything you don't need.

In Windows, unfortunately, there are no normal built-in tools for finding duplicate files. There is an option to do this via command line PowerShell, but this is very inconvenient, especially for beginners it will be difficult. Therefore it is easier to use third party programs. One of these is called AllDup. It is completely free, available in Russian, supported by all Windows operating systems and, finally, quite easy to use.

Downloading and installing the AllDup program

The program can be downloaded for free from the official AllDup website. Below is the link to the download section:

The program is available in two versions: regular installation and portable (Portable). The portable version is different in that it does not require installation on a computer, i.e. the program can be launched directly from the downloaded folder.

To download, click the button “Server #1”, or “Server #2” or “Server #3” (if the first button does not download, spare servers are given) under the required version programs.

Direct links to download the latest version (March 2017) of AllDup: standard version, portable version. Behind latest versions always refer to the official AllDup website!

Installing the program is very simple, one might say, it consists of successive clicks “Next”, no special settings no need to produce. That's why this process I won't consider it.

Learn more about the nuances of installing programs for Windows

Finding duplicates using AllDup

After installing the program, run it. The main window for search settings will open:

Setting up a search includes several steps:


These are all the main stages of setting up a search; the rest can be omitted.

Now, to start searching for duplicates, click the “Search” button at the top of the AllDup window:

The search process will begin.

How more files in the folders you specified is located on your disks, the longer the search will take.

After the search is completed, the program will display the found files with duplicates in the form of a table.

The first thing that is better to do right away is to save the search results, because if you now close this window with the results, then you will have to perform the search again. To save, click the button with the image of a floppy disk, or select top menu“Search Result” and click “Save Search Result”.

Now, even if you turn off your computer, and then launch the program again, you will be able to get to the search results again.

You can sort your search results by different parameters by clicking on the column headings in the table. The most useful sorting criterion, in my opinion, is file size. Therefore, if you want the largest files found to be displayed at the top of the table, then click on the “Size (Bytes)” column.

The next thing that is best to configure for ease of viewing results is the displayed size. Initially, the program shows the file size in bytes, which is not very convenient. It is better to display in megabytes or even gigabytes. To do this, click the button marked in the screenshot below (1), then check one of the options (2):

Now I’ll dwell on how to actually use the search results, how to view and remove unnecessary duplicates...

The program divides the found duplicates into so-called groups. One group is all found copies of the same file, including the original (it will also be displayed in this group).

To view duplicates of one of the groups, you need to open it by clicking on the arrow. Example:

Once you have expanded a specific group, you can check what kind of file it is by opening it. To do this, simply double-click on the file in the group or right-click and select “Open file”. The file will be opened via standard Windows program, through which you usually open all files of the selected type.

To delete duplicates, check them, right-click and select one of the options: delete file in Windows Recycle Bin or permanent deletion.

Accordingly, do not delete all files from the group, because this way you will delete both duplicates and the original at once! For example, if there are 3 files in a group, then by deleting 3 at once, you will delete both the original and 2 duplicates. In this case, to keep only a single copy of the file, you need to remove 2 files from the group.

This way you can check each group separately and remove duplicates. But if a lot of information has been found, it can be done simpler. Make sure that the program automatically selects all files in each group except one (i.e., only duplicates), after which you can get rid of all duplicates at once, or before that, go through and double-check whether everything marked is exactly to be deleted.

To automatically mark duplicates, go to the “Select” menu (1) and check and enable one of the options there (2), for example, “Select all files except the first file.”

As a result, the program will select 2 duplicates in each group, and leave the first file in the list unselected. That is, in this way you will mark 2 duplicates, and the original will remain unmarked. Or you can use the “Select” menu to try other options that are convenient for you.

Once the program has marked the files, you can double-check your selection if required. And to quickly delete everything unnecessary or perform some other action, click the button marked in the screenshot below:

In the window that opens you will see overall volume selected files, i.e. how much space the found duplicates take up and the number of selected files. At the bottom you need to select an action on the selected files. You can delete files through the recycle bin, delete them permanently (the “Delete files” item), copy or move files to a folder, and also rename the found duplicates. If you are sure that the marked files are duplicates and you no longer need them, then it is easier to delete them, but in any case, the choice is yours.

So choose required action(1) and click OK (2). You don't need to configure anything else here.

After this, the program will perform the action you selected on the previously marked files!

That's the whole process :) To exit the search results, simply close this window. If you have saved your search results, then if you need this result again, you can get to it through the “Search Result” section (1) in the main program window. The results you saved will be displayed in the table (2). To open desired result just double click on it.

Conclusion

AllDup - very convenient program to find duplicates of your files on your computer. In fact, there is nothing superfluous in the program; it has all the necessary tools, filters and parameters for fast processing a large volume of duplicates found. Of course there is also similar programs, which probably also do their job well. So far I have only tried AllDup and I don’t see any point in changing it yet.

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

And, although you can delete such unnecessary 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 specific type data.

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

Content:

Universal Applications

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

And, since the probability of the same number of bytes in different photos is practically zero, identical 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;
  • the ability to ignore certain files (with a certain size or creation date, as well as system or hidden ones).

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 performance), it’s worth downloading these applications as well.

7. Duplicate Cleaner Free

The utility for detecting copies of any file Duplicate Cleaner Free is different 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 of individual menu items.

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 in by and large, only by 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 duration of the scan 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, sizes.

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 correctly translated (and sometimes even provided only in English).

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

Rice. 13. Application Image Comparer- a good way to find duplicate pictures.

If you are working with large quantities information in Excel and regularly add it, for example, data about school students or company employees, then duplicate values ​​may appear in such tables, in other words, duplicates.

In this article we will look at how to find, select, delete and count the number of duplicate values ​​in Excel.

How to find and highlight

You can find and highlight duplicates in a document using conditional formatting in Excel. Select the entire data range in the desired table. On the “Home” tab, click on the button "Conditional Formatting", select from menu “Rules for highlighting cells” – "Duplicate values".

In the next window, select from the drop-down list "repetitive", and the color for the cell and text in which to paint the found duplicates. Then click "OK" and the program will search for duplicates.

IN Excel example I highlighted in pink all the same information. As you can see, the data is not compared row by row, but highlighted identical cells in columns. Therefore, the cell “Sasha V.” is highlighted. . There may be several such students, but with different last names.

How to count

If you need to find and count the number of repeating values ​​in Excel, we will create a summary for this Excel spreadsheet. Add the “Code” column to the original one and fill it with “1”: put 1, 1 in the first two cells, select them and drag them down. When duplicates are found for rows, the value in the Code column will be incremented by one each time.

Select everything together with the headings, go to the “Insert” tab and press the button "Pivot table".

To learn more about how to work with pivot tables in Excel, read the article by following the link.

In the next window, the cells of the range are already indicated; use a marker to mark “On new leaf" and click "OK".

On the right side, drag the first three headings into the area "Line titles", and drag the “Code” field to the “Values” area.

As a result we get pivot table without duplicates, and the “Code” field will contain numbers corresponding to repeating values ​​in the source table - how many times this line was repeated in it.

For convenience, let's highlight all the values ​​in the column “Amount by field Code”, and sort them in descending order.

I think now you can find, select, delete and even count the number of duplicates in Excel for all rows of the table or only for selected columns.

Rate this article:

Perhaps everyone who works with data in Excel is faced with the question of how to compare two columns in Excel for similarities and differences. There are several ways to do this. Let's take a closer look at each of them.

How to compare two columns in Excel row by row

When comparing two columns of data, it is often necessary to compare the data in each individual row for matches or differences. We can do such an analysis using the function. Let's see how this works using the examples below.

Example 1: How to compare two columns for matches and differences in the same row

In order to compare the data in each row of two columns in Excel, we write simple formula. The formula should be inserted into each row in the adjacent column, next to the table in which the main data is located. Having created a formula for the first row of the table, we can stretch/copy it to the remaining rows.

In order to check whether two columns of the same row contain the same data, we need the formula:

=IF(A2=B2; “Match”; “”)

The formula that determines the differences between the data of two columns in one row will look like this:

=IF(A2<>B2; "Do not match"; “”)

We can fit the match and difference test between two columns on one line in one formula:

=IF(A2=B2; “Match”; “Do not match”)

=IF(A2<>B2; "Do not match"; "Match up")

An example calculation result might look like this:

To compare data in two columns of the same row in a case-sensitive manner, use the formula:

=IF(MATCH(A2,B2), “Match”, “Unique”)

How to compare multiple columns for matches in one Excel row

Excel allows you to compare data in multiple columns of the same row by following criteria:

  • Find lines with the same values in all columns of the table;
  • Find rows with the same values ​​in any two columns of the table;

Example 1. How to find matches in one row in multiple columns of a table

Let's imagine that our table consists of several columns of data. Our task is to find rows in which the values ​​​​match in all columns. They will help us with this Excel functions And . The formula for determining matches will be as follows:

=IF(AND(A2=B2,A2=C2); “Coincide”; “ “)

If our table has a lot of columns, then it will be easier to use the function in combination with:

=IF(COUNTIF($A2:$C2,$A2)=3;”Match”;” “)

In the formula, “5” indicates the number of columns of the table for which we created the formula. If your table has more or less columns, then this value should be equal to the number of columns.

Example 2: How to find matches of the same row in any two columns of a table

Let's imagine that our task is to identify from a table with data in several columns those rows in which the data is the same or repeated in at least two columns. The and functions will help us with this. Let's write a formula for a table consisting of three columns of data:

=IF(OR(A2=B2,B2=C2,A2=C2);”Match”;” “)

In cases where there are too many columns in our table, our formula with the function will be very large, since in its parameters we need to specify the matching criteria between each column of the table. An easier way, in this case, is to use the .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unique string”; “Not unique string”)

=IF(COUNTIF($B:$B,$A5)=0; “There are no matches in column B”; “There are matches in column B”)

This formula checks the values ​​in column B to see if they match the cells in column A.

If your table consists of a fixed number of rows, you can specify a clear range in the formula (for example, $B2:$B10). This will speed up the formula.

How to compare two columns in Excel for matches and highlight them in color

When we look for matches between two columns in Excel, we may need to visualize the found matches or differences in the data, for example using color highlighting. The easiest way to highlight matches and differences in color is to use Conditional Formatting in Excel. Let's look at how to do this using the examples below.

Finding and highlighting matches in color in multiple columns in Excel

In cases where we need to find matches in several columns, then for this we need:

  • Select the columns with data in which you want to calculate matches;
  • On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
  • In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color repeating values ​​will be highlighted in. Click “OK” button:
  • After this, matches will be highlighted in the selected column:

Find and highlight matching rows in Excel

Finding matching cells with data in two or more columns and searching for matches of entire rows with data are different concepts. Please note the two tables below:

The tables above contain the same data. Their difference is that in the example on the left we were looking for matching cells, and on the right we found entire repeating lines with data.

Let's look at how to find matching rows in a table:

  • To the right of the table with the data, we will create an auxiliary column in which, opposite each row with the data, we will put a formula that combines all the values ​​of the table row into one cell:

=A2&B2&C2&D2

In the auxiliary column you will see the combined table data:

Now, to determine the matching rows in the table, do next steps:

  • Select the area with data in the auxiliary column (in our example this is a range of cells E2:E15 );
  • On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
  • In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color repeating values ​​will be highlighted in. Click “OK” button:
  • After this, duplicate lines will be highlighted in the selected column:


Finding duplicates in Excel may not be an easy task, but if you are armed with some basic knowledge, you will find several ways to tackle it. When I first thought about this problem, I quickly came up with a couple of ways to find duplicates, and after thinking about it a little, I discovered a few more ways. So, let's look at a couple of simple ones first, and then move on to more complex methods.

The first step is that you need to put the data into a format that makes it easy to manipulate and change. Creating headings on the top row and placing all the data under those headings allows you to organize your data into a list. In a word, the data turns into a database that can be sorted and various manipulations performed with it.

Find duplicates using built-in Excel filters

By organizing your data in the form of a list, you can apply various filters to it. Depending on the data set you have, you can filter the list by one or more columns. Since I'm using Office 2010, all I need to do is select top line, which contains the headers, then go to the tab Data(Data) and press command Filter(Filter). Downward-pointing triangular arrows (drop-down menu icons) will appear next to each heading, as in the image below.

If you click one of these arrows, a filter drop-down menu will open that contains all the information about this column. Select any item from this list and Excel will display the data according to your selection. This quick way summarize or see the volume of selected data. You can uncheck the item Select All(Select All), and then select one or more of the items you want. Excel will only show rows that contain the items you selected. This makes it much easier to find duplicates if there are any.

After setting up the filter, you can remove duplicate rows, summarize subtotals, or additionally filter the data by another column. You can edit the data in the table as you need. In the example below I have the elements selected XP And XP Pro.

As a result of the filter, Excel displays only those rows that contain the elements I selected (that is, people on whose computers XP and XP Pro are installed). You can choose any other combination of data, and if necessary, even set up filters in several columns at once.

Advanced filter to find duplicates in Excel

On the tab Data(Data) to the right of the command Filter(Filter) there is a button for filter settings – Advanced(Additionally). This tool is a little more difficult to use and requires a bit of setup before you can use it. Your data should be organized as described previously, i.e. like a database.

Before you can use an advanced filter, you must set up a criterion for it. Look at the picture below, you can see a list with data, and on the right in the column L criterion is specified. I have written the column heading and criterion under one heading. The picture shows a table of football matches. Requires it to only show home meetings. That's why I copied the title of the column where I want to filter and below it I placed the criterion (H) that I need to use.

Now that the criterion is configured, select any cell of our data and press the command Advanced(Additionally). Excel will select the entire list of data and open this dialog box:

As you can see, Excel has selected the entire table and is waiting for us to specify a range with a criterion. Select the field in the dialog box Criteria Range(Condition Range), then select the cells with your mouse L1 And L2(or those containing your criterion) and click OK. The table will display only those rows where in the column Home / Visitor worth the value H, and will hide the rest. Thus, we found duplicate data (one column at a time), showing only home meetings:

This is a fairly simple way to find duplicates, which can help save time and get necessary information fast enough. You need to remember that the criterion must be placed in a cell separate from the data list so that you can find it and use it. You can change the filter by changing the criterion (mine is in cell L2). In addition, you can disable the filter by clicking the button Clear(Clear) tab Data(Data) in group Sort & Filter(Sort and filter).

Built-in tool to remove duplicates in Excel

Excel has a built-in function Remove Duplicates(Remove duplicates). You can select a column of data and use this command to remove all duplicates, leaving only unique values. Use the tool Remove Duplicates(Delete duplicates) can be done using the button of the same name, which you will find on the tab Data(Data).

Be sure to select which column you want to keep only unique values. If the data does not contain headers, the dialog box will show Column A, Column B(column A, column B) and so on, so it’s much more convenient to work with headings.

When you're done with the settings, click OK. Excel will display an information window with the result of the function (example in the figure below), in which you also need to click OK. Excel automatically eliminates rows with duplicate values, leaving you with only unique values ​​in the columns you select. By the way, this tool is present in Excel 2007 and newer versions.

Finding duplicates using the Find command

If you need to find in Excel a small amount of duplicate values, you can do this using search. Go to the tab Hom e (Home) and click Find & Select(Find and highlight). A dialog box will open in which you can enter any value to search in your table. To avoid typos, you can copy the value directly from the data list.

If the volume of information is very large and you need to speed up the search, select the row or column in which you want to search, and only then start the search. If you don't do this, Excel will search through all available data and find unnecessary results.

If you need to search through all available data, perhaps the button Find All(Find All) will be more useful for you.

Finally

All three methods are easy to use and will help you find duplicates:

  • Filter– Ideal when your data contains multiple categories that you may need to split, summarize, or remove. Creating subsections is the best use for an advanced filter.
  • Removing duplicates will reduce the amount of data to a minimum. I use this method when I need to make a list of all the unique values ​​of one of the columns, which I later use for vertical search using the VLOOKUP function.
  • I use the command Find(Find) only if you need to find a small number of values, and the tool Find and Replace (Find and Replace) when I find errors and want to correct them at once.

This is not an exhaustive list of methods for finding duplicates in Excel. There are many ways, and these are just a few of them that I use regularly in my daily work.