Unique values by a column

[etrader58]

I have successfully used the excel advanced filter for creating unique values for a one-column list. But how I can make the unique values in a multi-column list. I mean making the unique list by the values of one column only (not the whole row).

Example

Column A | Column B
1: TEST1 | VALUE1
2: TEST2 | VALUE2
3: TEST3 | VALUE1

I want to make the unique list by the values of column B only. I want to remove the row 3 in this example; but excel treats it as a unique row.

 

[JackDanIce]

Maybe use a 3rd column with formula:

=A1&"@"&B1

Copy and paste the values, then base your advanced filter on values in this new column which should be unique. Later you can use a search and replace to remove '@' and replace with a space or other.

 

[etrader58]

But this exactly do what excel does. It creates

1: TEST1@VALUE1
3: TEST3@VALUE1

These two rows are uniques and excel keeps them; BUT I want to remove the row 3, because it contains VALUE1 (which is identical to that of row 1).

 

[Sektor]

Sub DeleteDups()
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=2, Header:=xlNo
End Sub

 

[JackDanIce]

Took too long to edit my macro so have asked for that post to be removed. This is what I meant to suggest instead:

Sub RemoveDupes ()
Dim i As Long, j As Long
Application.ScreenUpdating = False
i = Range("B" & Rows.Count).End(xlUp).row
j = 1
Range("A1:B" & i).Sort key1:=Range("B1"), order1:=xlAscending
Do While Not (IsEmpty(Range("B" & j)))
    If Range("B" & j) = Range("B" & j + 1) Then
        Range("A" & j + 1 & ":B" & j + 1).ClearContents
        Range("A1:B" & i).Sort key1:=Range("B1"), order1:=xlAscending
        j = j - 1
    End If
    j = j + 1
Loop
Application.ScreenUpdating = True
End Sub

If your data has a header row then change the red 1 above to 2

 

[etrader58]

Sub DeleteDups()
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=2, Header:=xlNo
End Sub

Thanks it worked like a charm; just one simple question:

It keep the first occurrence of any value, as I tested. It is reliable? Because I mix two lists, and it is very important for me to keep the values from first list (not the second one) if there is a duplicate. In the above example:

1: TEST1 | VALUE1
3: TEST3 | VALUE1

I want to remove the row 1 NOT row 3. This macro works perfectly, but I just want to be sure it has the same behavior in long lists.

 

[Sektor]

Here's what removing doing:
1. It takes range A1:B3.
2. Watches for argument Coumns which contains array of columns to remove. In our case it's B column.
3. It searches all duplicates in B column and removes ENTIRE row with duplicate.

If I'd point "Columns:=Array(1, 2)", then code would search for duplicates in A:B rows.

 

[jasmith4]

JackDanIce: That won't work, because I think etrader58 wants uniqueness only with respect to Column B. He wants the third row removed, because VALUE1 in cell B3 matches VALUE1 in B1.

etrader58: The problem is that Excel's uniqueness test applies only to the data it copies over, so you have to do it another way. Here's a tricky way I devised:

First, since you'll be using Data / Filter / Advanced, you have to add headings, and this also involves criteria and output ranges, so please set up your sheet as follows, where columns A and B are your original data:

# --A-- --B--- --C--- --D-- --E--- --F-- --G-- --H---
1 TESTS VALUES FIRSTS blank FIRSTS blank TESTS VALUES
2 Test1 Value1 (fmla) blank 1
3 Test2 Value2 (copy)
4 Test3 Value1 (copy)

A2:B4 contain your data, columns D and F have nothing in them, C1 and E1 have the same new heading, E2 has the constant 1, and the headings in G1:H1 match those in A1:B1. Matching headings is crucial!

As for C2, enter into it the formula "=COUNTIF($B$2:$B2,B2)" being very careful with the dollar-signs. What it does is to count the number of occurrences of the text in the cell to its left from the top of the list ($B$2) to the entry thus far. Copy-and-paste C2 down to C3 and C4, click C3 and C4 to inspect them, and you'll see:

Cell Formula as copied down Value
C3 =COUNTIF($B$2:$B3,B3) 1
C4 =COUNTIF($B$2:$B4,B4) 2

NOW start a Data / Filter / Advanced operation, with the following settings:

Copy to another location
List range: $A:$C (the whole columns)
Crit range: $E$1:$E$2
Copy to: $G:$H (the whole columns)
Unique records only: OFF -- it's unnecessary now.
​

Then you'll get what you want.

 

[etrader58]

Thanks for informative description. I just meant when finding duplicates, it will keeps the first occurrence of a value and deletes the second, third, ... occurrences of the value, am I right?

When there are duplicates in rows 23, 435, and 989; it will keep row 23, and deletes rows 435 and 989, right?

I just want to be sure that it will not delete rows 23 and 435; then keeping row 989.

 

[jasmith4]

Yes -- only the first of all non-unique values will be taken. The criterion asks whether the formula equals 1, and on the 23rd, 435th and 989th rows, the values will equal 1, 2 and 3, respectively. This is because the formulas in those rows will look at B2:B23, B2:B435 and B2:B989.