Hi all! Some will understand me now, but some will not (who do not work with text that often). In general, I often work with documents and I always have the question of how to remove duplicate lines...
There are duplicates everywhere, for example for a lot of programs. And, in order to delete identical text strings, there are few programs. Yes, there are none at all! No matter how much I searched, now I’ll show you 2 programs through which you can do this. And then one seems to be self-written, from the author, who also ran out of patience to look for them)))
1 way. How to remove duplicate rows in Excel
This method will be the first, because Most users have this program installed. It is included in the Microsoft Office package. Launch Excel and insert the text that needs to be sorted.
Your text column should now be highlighted. When you select it, click remove duplicates.
A window will appear in which we select the desired column. And click OK.
A window will appear with a message about deleting duplicates.
That's it, we see that the duplicates have been deleted.
2 way to remove duplicate rows
On help will come a program that I saw purely by chance. By the way, not only this functionality is implemented in it. Perhaps someone will use it for another purpose. Downloading...
I specifically wrote several identical and duplicate lines.
Let's launch the program. Select remove duplicates and select and remove duplicates button. Select your file.
Good evening everyone! Have you ever worked with data in Excel whose string value exceeds a couple of tens of thousands? But as a person who creates and promotes websites and online stores, I have to deal with this quite often, especially when it comes to loading and unloading data from the supplier to the online store website. This note was not born out of nowhere, but out of the heat of the moment, so to speak! Today I made an upload on my topic (see portfolio) and after I downloaded the price list from the supplier and opened it in Excel (before uploading it to the site, I first check everything, in case of errors and accidental changes to the columns on the part of the supplier) and saw that out of 25 thousand lines of more than 6-8 thousand are duplicates, we won’t discuss why and why the supplier does this now, we don’t want to waste any effort or time on this, but we just understand that the programmer did it this way and it can’t be done any other way!
After much deliberation, I decided to give you instructions on how to delete duplicate rows without shifting the values up.
Removing Duplicates in Microsoft Excel
For me, a person who spends time on vacation and works with mobile internet the speed of which is measured from 1-2 megabits, it makes no sense to pump such a quantity of goods with photographs into empty space and the time is empty and will consume quite a lot of traffic, so I decided to simply delete the duplicate products and then I was faced with the fact that it is not possible to delete duplicate values in the column it’s that simple, because it’s standard excel function 2010 does this clumsily and after removing the duplicate, it moves the lower values up and in the end everything in the document will be mixed up and there will be a mess.
This article will present two options for solving the problem.
Option 1 - Standard function in Excel - Remove duplicates
I can’t miss this option, even though it’s the most primitive, but maybe this is exactly what you were looking for for your situation, so let’s look at the functionality that comes out of the Excel box itself
To do this, select those columns or area in which you need to remove duplicates and go to the menu Data and then we'll choose Remove duplicates, after which we will delete duplicates, but there will be a shift of cells, if this is not critical for you, then this method is yours!
Option 2 - Mark duplicate rows as False or True
This option is the simplest and from here a bird immediately flies out which will limit this option in action, namely that it can be used if all your duplicate values are in order, and not scattered throughout the document
For example, let’s take two columns of data, in one (example 1) the duplicate values are repeated, and in (example 2) they are inconsistent and do not follow each other.
In example 1, we must press the exactly sign in the cell next to it and select the first and lower values so that the formula is like this:
and press enter, and depending on the data, the value False or True should appear in this cell
FALSE - if A1 is not equal to A2
True - if A1 is exactly A2
if you apply this option on the Example2 column, then, as you understand, the value will be False everywhere
This option is good only in rare cases, but you also need to know it, its limitation is that this formula compares itself and the next value, that is, it is only applicable to two cells at a time, and not to the entire column. But if you have data like column Example2, then read on)
Option 3 - Remove duplicates in a column
This option is more complicated, but it will solve your problem 100% and answer all your questions right away.
As you can see, we have a column in which all the values are out of order and they are mixed
Just like last time, we insert the following formula into the adjacent cell
IF(COUNTIF($A$2:A2,A2)>1;"";A2)
After applying which we will have either a starting cell or a value from the cell opposite.
from our example it is immediately clear that there were two duplicates in this column and this formula saved us a lot of time, and then we filter the second column and select empty cells in the filter and then delete the rows, that’s all)
Thus, in the document that I downloaded from the supplier, I created an empty column in front of the article and then applied this formula and after filtering I received a document that was 6-8 thousand lines less and most importantly, after removing duplicates, my values did not go up, everything stood at in their places
I hope the article was useful, if you don’t understand, I attached a video to the channel, watch it or ask questions,
There are no similar entries.
Duplicate data in Excel can lead to many problems when working with data. It doesn't matter whether you are importing data from any database, receiving it from a colleague or friends. The more data in your file, the more difficult it is to find and remove duplicates in Excel.
In this article, we will take a closer look at effective practices for finding and removing duplicates.
Finding and highlighting duplicates with color in Excel
Duplicates in tables can occur in different forms. These can be duplicate values in one column or in several, as well as in one or more rows.
Finding and highlighting duplicates in one column in Excel
The easiest way to find and highlight duplicates in Excel is to use conditional formatting.
How to do it:
- Let's select the area with the data in which we need to find duplicates:
- 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 “Repeated” in the left drop-down list, and in the right drop-down list, select what color duplicate values will be highlighted. Click “OK” button:
- After this, in the selected column, duplicates will be highlighted in color:
Clue: Don't forget to check your table data for extra spaces. For this it is better to use TRIM function(SPACES).
Finding and highlighting duplicates in multiple columns in Excel
If you need to calculate duplicates in multiple columns, then the process for calculating them is the same as in the example described above. The only difference is that for this you need to select not one column, but several:
- Select the columns with data in which you need to find duplicates;
- 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, duplicates in the selected column will be highlighted in color:
Find and highlight duplicate rows in Excel
Finding duplicates of duplicate cells and entire rows of 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 duplicate cells, and on the right we found entire repeating rows of data.
Let's look at how to find duplicate rows:
- 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 identify duplicate rows in a 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 the repeating values will be highlighted in. Click “OK” button:
- After this, duplicate lines will be highlighted in the selected column:
In the example above, we highlighted the lines in the created auxiliary column.
But what if we need to highlight the rows not in the auxiliary column, but the rows themselves in the data table?
To do this, let's do the following:
- Just like in the example above, we will create an auxiliary column, in each row of which we will enter the following formula:
=A2&B2&C2&D2
Thus, we will receive the collected data of the entire table row in one cell:
- Now, let's select all the table data (except for the auxiliary column). In our case these are range cells A2:D15 ;
- Then, on the “Home” tab on the Toolbar, click on the “Conditional Formatting” -> “Create Rule” item:
- In the “Create a formatting rule” dialog box, click on the “Use a formula to determine the cells to format” option and in the “Format values for which the following formula is true” field, insert the formula:
=COUNTIF($E$2:$E$15,$E2)>1
- Don't forget to set the format of the duplicate lines found.
This formula checks the range of data in the auxiliary column and, if there are duplicate rows, highlights them in color in the table:
How to remove duplicates in Excel
Above we learned how to find duplicates and how to highlight them with color. Below you will learn how to remove them.
How to remove duplicates in one Excel column
If your data is located in one column and you want to remove all duplicates, then do the following:
- Select the data;
- Go to the Toolbar, “Data” tab –> “Working with data” section -> “Remove duplicates”:
- In the Remove Duplicates dialog box, check the box next to “My data contains headers” if the column range you selected has a header. Also, make sure that the column you need is selected in the “Columns” menu:
- Click “OK”
After this, the system will remove all duplicates in the column, leaving only unique values.
Advice. Be sure to do backup copy your data before any operations involving the removal of duplicates. You can also delete duplicates on separate sheets to avoid accidentally deleting data.
How to Remove Duplicates in Multiple Columns in Excel
Let's imagine that we have sales data like the table below.
Good day!
With the popularization of computers over the past 10 years, there has also been a popularization of creating reports (documents) in Excel.
And in any respect large document There are duplicate rows, especially if you collected it from several other tables. These duplicates can greatly interfere with further editing of the table, and therefore they need to either be selected or deleted altogether...
Actually, I have been approached with similar questions more than once or twice, and I decided to document the work with duplicates in a separate short article (which you are reading now). I will give the most frequently encountered problems and show their solution.
Note: all examples below will be presented in Office 2016 (also relevant for Office 2013, 2010, 2007). I recommend always using relatively new ones Office versions: they are both faster to work with and easier ☻.
Simple auto-removal of duplicate lines
Let's imagine one of the most popular tasks: there is a list of lines with the full names of people, there are a lot of matches in the lines, you need to leave only unique values (by the way, this is just an example, all matches with real people are random).
The problem is illustrated in the screenshot below (this is an example: it is clear that 2 lines can be deleted by hand, but in reality there can be 2000 or 20000 lines, where “hands” will be powerless...!).
To remove duplicate rows in Excel, you must:
Copying unique rows (data) to a new location
If you do not want to touch and change the current data (current table), then you can simply copy only unique rows(values) from it to a new table. This is very convenient when you want to visually compare what happened and what happened (and are also worried about old data that may still be useful).
How to do it:
Finding and highlighting duplicate values
In cases where you do not need to delete duplicates or copy unique values to a new location, you can simply find and highlight them (and you can highlight both unique values and duplicate ones). Usually, this needs to be done when further adjustments of lines are to be made (they can be found conveniently and quickly).
Manually finding and removing duplicate values in an Excel spreadsheet, especially in large documents, is a very risky and impractical task. It is possible that when you visually check the cells, you may miss duplicates. And then, a colossal amount of time is spent deleting each cell.
In this article, we will look at how you can remove duplicates in Excel using various automated standard functions.
Advice! To make it easier to familiarize yourself with ways to remove duplicate rows, columns, and cell values, open a draft template or a copy of the project you are working on in the editor, so that by mistake during editing you do not lose valuable data or break the format of the original.
Method No. 1
1. While holding down the left mouse button, use the cursor to mark the boundaries of the table area (individual rows, columns) where repetitions need to be removed. Or select the entire project.
2. In the editor menu, go to the “Data” tab.
3. In the “Working with Data” block, click the “Remove Duplicates” button.
4. In the panel that appears, select the columns in which you want to remove identical values by setting labels. If there are many elements, use for quick setup removing the “Select All” and “Deselect” options. Click OK.
5. Once the table rows and columns are cleared of duplicates, a message will appear indicating how many unique values are left.
Advice! Repetitions can be restored immediately after deletion by clicking the left arrow icon in the upper left corner of Excel.
Method No. 2
1. Click on the table you are editing.
2. On the “Data” tab, in the “Sorting and Filter” block, click the “Advanced” subsection.
If you need to create a new table containing only unique source cells:
1. In the “Advanced Filter” panel, click the “Copy result to another location” radio button.
2. Click the button located on the right side of the “Place result in range” field.
3. Click on a free Excel workspace where you will need to place the filtered table. After clicking, the cell code will appear in the field. Close it and go to filter options.
4. Click the “Unique records only” box and click “OK”.
5. After filtering, a version of the original table without duplicates will appear in the specified location.
To edit a document without making copies:
- in the “Advanced Filter” panel, set the processing mode to “Filter list in place”;
- Click the mouse to enable the “Only unique records” add-on;
- Click “OK”.
