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.
This question is related to
excel
excel-formula
excel-2010
Another approach, since Excel 2016, is to use Power Query.
Howto:
Data
> From a table or a range
,Remove duplicates
,Close and load
, choose where you want the result, and you're done, like this.Refresh
.Benefits :
Drawbacks :
To remove duplicates from a column
While column B is still selected, in the formula input box, enter
=IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
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.
=SORT(UNIQUE(A:A))
The above formula works best if you want to list unique values in a column.
In my case the excel was frozen when using the formula of
B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
because there was many rows (10000). So I did in another way which I show below.
I have copied my original list to a second column and then with the function of Excel "remove duplicates" I could find the list of unique values.
Copied from Microsoft Office Website:
Select all the rows, including the column headers, in the list
you want to filter.
Click the top left cell of the range, and then drag 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 the duplicate rows are hidden.
On the Edit menu, click Office Clipboard.
The Clipboard task pane is displayed.
Make sure the filtered list is still selected, and then click Copy Copy button.
The filtered list is highlighted with bounding outlines and the selection appears as an > > item 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 is deleted.
In the Clipboard, click on the filtered list item.
The filtered list appears in the same location as the original list.
Source: Microsoft Office Website (link removed, cause dead)
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))
On 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 pasted down. It will result 0, after the last unique match. If this is unwanted, put some IF statement around to exclude this.
Edit:
Easier than an IF statement, at least for text-values:
B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),"")
I have a list of color names in range A2:A8, in column B I want to extract a distinct list of color names.
Follow the below given steps:
=IF(COUNTIF(A$2:A2,A2)=1,A2,””)
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.
All you have to do is : Go to Data tab Chose advanced in Sort & Filter In actions select : copy to another location if want a new list - Copy to any location In list range chose the list you want to get the records off . And the most important thing is to check : Unique records only .
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_x005F_x0020_Type&$orderby=Expense_x005F_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_x005F_x0020_Type;
if(i == 0)
{
myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";
}
else
if(myValue != data.results[i-1].Expense_x005F_x0020_Type)
{
myhtml += "<option value='"+myValue+"'>"+myValue+"</option>";
}
else
{
}
}
$("#mySelect1").append(myhtml);
});
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)
Honestly I followed these examples to a tee and they simply didn't work. What I ended up doing after struggling pointlessly trying to get Excel to work was to just copy the entire contents of my column to NotePad++ where I was able to find an easy solution within minutes. Take a look at this: Removing duplicate rows in Notepad++
Edit: Here is a brief overview of how to do it in TextFX:
Plugins -> Plugin Manager -> Show Plugin Manager -> Available tab -> TextFX -> Install
After TextFX is installed in NotePad++, then you select all your text you want to remove duplicates from, then make sure to check: TextFX -> TextFX Tools -> Sort outputs only UNIQUE lines
Then click "sort lines case sensitive" or "sort lines case insensitive" and it will perform the unique sort.
Source: Stackoverflow.com