Ignore duplicates and create a new list of unique values ​​in Excel

Question

I have a column of values ​​that often appears as duplicates. I need to create a new column with unique values ​​based on the first column as follows:

Column A Column B aa ab bc c c 

This column B should actually appear on another sheet in the same book, so I assume that it will work with the sheet2!A1 style format.

I was not lucky with the "Data / Filter" menu options, since this seems to work only with the command. I need column B to automatically update whenever a new value is entered in column A.

Answer 1

The answer to totero is correct. The link is also very useful.

Basically, you need the following formula:

 B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) 

Then press ctrl + shift + enter (or it will not work using an array formula).

Two important things to keep in mind here: the full list is in cells A2:A20 , then this formula should be inserted in cell B2 (Not B1 , as this will give you a circular link). Secondly, it is an array formula, so you need to press ctrl + shift + enter or it will not work correctly.

Answer 2

There is a good guide on how to do this here .

Basically something similar to:

 =INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0)) 

Answer 3

In my case, the limit was frozen using the formula

B2 = INDEX ($ A $ 2: $ A $ 20, MATCH (0, COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 20), 0))

because there were a lot of lines (10000). So I did it differently, as shown below.

I copied my original list to the second column, and then using the "delete duplicates" function in Excel, I was able to find a list of unique values.

Copied from the Microsoft Office website:

 Select all the rows, including the column headers, in the list 

which you want to filter.

Click the top left cell of the range, and then drag it to the bottom right cell.

 On the Data menu, point to Filter, and then click Advanced Filter. In the Advanced Filter dialog box, click Filter the list, in place. Select the Unique records only check box, and then click OK. 

The filtered list is displayed and duplicate rows are hidden.

 On the Edit menu, click Office Clipboard. 

The Clipboard taskbar is displayed.

 Make sure the filtered list is still selected, and then click Copy Copy button. 

The filtered list is highlighted with bounding contours, and the selection is displayed as an element → at the top of the clipboard.

 On the Data menu, point to Filter, and then click Show All. 

The original list is re-displayed.

 Press the DELETE key. 

The original list has been deleted.

 In the Clipboard, click on the filtered list item. 

The filtered list is displayed in the same place as the original list.

Source: Microsoft Office website (link removed, reason dead)

Answer 4

In a sorted column, you can also try this idea:

 B2=A2 B3=IFERROR(INDEX(A:A,MATCH(B2,A:A,1)+1),"") 

B3 can be glued down. This will be the result 0 after the last unique match. If this is undesirable, add some IF statement to exclude it.

Edit:

Easier than an IF statement, at least for text values:

 B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),"") 

Answer 5

To remove duplicates from a column

• Sort values ​​in column A A-> Z
• Select Column B

• While column B is still selected, in the formula input field, enter

   =IF(TRIM(A1)=TRIM(A2),"",TRIM(A1)) 

• While column B is still selected, choose Edit → Fill → Down (in new versions, just select cell B1 and drag the outer frame to expand all the way down in the column)

Note : if column B is on another sheet, you can do Sheet1! A1 and Sheet1! A2.

Answer 6

In a sheet of a sheet of a sheet containing a list:

 Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDest As Range If Not Intersect(Target, Me.Columns(1)) Is Nothing Then Set rngDest = ThisWorkbook.Sheets("Sheet2").Range("A1") Me.Range(Me.Range("A2"), Me.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=rngDest, Unique:=True End If End Sub 

Answer 7

I have a list of color names in the range A2: A8, in column B. I want to extract a list of color names.

Follow the steps below:

• Select cell B2; write a formula to get unique values ​​from the list.
• =IF(COUNTIF(A$2:A2,A2)=1,A2,"")
• Press Enter on your keyboard.
• The function will return the name of the first color.
• To return the value for the remaining cells, copy the same formula down. To copy a formula in the range B3: B8, copy the formula in cell B2 by pressing CTRL + C on the keyboard and paste it into the range B3: B8 by pressing CTRL + V.
• Here you can see the result on which we have a unique list of color names.

Answer 8

So, for this task, First sort the data in order from A to Z or Z to A, then you can just use one simple formula, as follows:

 =IF(A2=A3, "Duplicate", "Not Duplicate") 

The above formula states that if the data in column A2 (A is the column and 2 is the row number) is similar to A3 (A is the column and 3 is the row number), then it will print. Duplicate else will print Not Duplicate.

Let's look at an example: column A consists of an email address in which there are some duplicates, so in column 2 I used the above formula, which in the results showed me two duplicate cells: rows 2 and 6.

You have duplicate data that simply puts a filter on your leaflet and makes visible only duplicate data and removes all unnecessary data.

Answer 9

Honestly, I followed these examples into a tee, and they just didn't work. What I ended up doing, trying to get Excel to work, was just copying the entire contents of my column into NotePad ++, where I could find an easy solution in minutes. Take a look at this: Removing duplicate lines in Notepad ++

Edit: Here is a brief overview of how to do this in TextFX:

Plugins → Connection Manager → Show Connection Manager → Available tab → TextFX → Install

After TextFX is installed in NotePad ++, you select all the text from which you want to remove duplicates, then make sure you check: TextFX → TextFX Tools → Sort only for UNIQUE lines

Then click “Case-sensitive string sorting” or “Case-sensitive string sorting” and it will perform a unique look.

Answer 10

Find here the above error control formula

 =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") 

where: (B2: B9 is the column data you want to extract unique values, D1 is the specified cell where your formula is located)

Answer 11

All you have to do is: Go to the "Data" tab. Select "Advanced" in the "Sort & Filter" section. In the actions, select: copy to another place, if you want a new list, - Copy to any place. In the list range, select the list from which you want to get entries. And the most important thing is to check: only unique entries.

Answer 12

 =SORT(UNIQUE(A:A)) 

The above formula works best if you want to list unique values ​​in a column.

Answer 13

Another approach, starting with Excel 2016, is to use Power Query.

How:

• select data (including field name),
• use the menu Data > From a table or a range ,
• (Excel will change your sheet to an Excel spreadsheet, which is very convenient)
• in the Power Query editor, right-click ColumnA (column heading) and select Remove duplicates ,
• in the menu, select Close and load , select where you want the result, and you're done, like that .
• Whenever you want to update the result table, right-click it and select " Refresh .

Benefits:

• it only uses the processor when updating manually, which is very convenient for long lists,
• if you're interested, this offers many other powerful options.

Disadvantages:

• it does not update on the fly (you need to right-click and update the table of results),
• people with an older version of Excel will not be able to update the result table.

Answer 14

A MODERN approach is to consider cases where a column of information comes from a web service, such as an OData source. If you need to generate filter selection fields with massive data that have replicated values ​​for a column, consider the code below:

 var CatalogURL = getweb(currenturl) +"/_api/web/lists/getbytitle('Site%20Inventory%20and%20Assets')/items?$select=Expense_x0020_Type&$orderby=Expense_x0020_Type"; /* the column that is replicated, is ordered by <column_name> */ OData.read(CatalogURL, function(data,request){ var myhtml =""; var myValue =""; for(var i = 0; i < data.results.length; i++) { myValue = data.results[i].Expense_x0020_Type; if(i == 0) { myhtml += "<option value='"+myValue+"'>"+myValue+"</option>"; } else if(myValue != data.results[i-1].Expense_x0020_Type) { myhtml += "<option value='"+myValue+"'>"+myValue+"</option>"; } else { } } $("#mySelect1").append(myhtml); });