Ignore Duplicates and Create New List of Unique Values in Excel



Answers

There is a good guide of 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))
Question

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

Column A   Column B  
a          a
a          b
b          c
c
c

This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the sheet2!A1 style format.

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A.




In the worksheet module for the sheet containing the 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



Find here mentioned above formula with error control

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

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




So for this task First Sort your data in order from A to Z or Z to A then you can just use one simple formula as stated below:

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

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

Lets consider an example, Column A consists Email address in which some are duplicate, so in Column 2, I used the above stated formula which in results displayed me the 2 duplicates cells one is Row 2 and Row 6.

One you got the duplicate data just put filter on your sheet and make visible only the duplicate data and delete all the unnecessary data.




The MODERN approach is to consider cases where column of information come from a web service such as an OData source. If you need to generate a filter select fields off of massive data that has replicated values for the 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);

        });



To remove duplicates from a column

  1. Sort the values in column A A->Z
  2. Select column B
  3. While column B is still selected, in the formula input box, enter

    =IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
    
  4. While Column B is still selected, select Edit -> Fill -> Down (in newer versions, simply select cell B1 and pull down the outer box to expand all the way down in the column)

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




Links