finding unique values and removing all duplicates

[waylonk]

I have a spread sheet with 4 columns and 900 rows I am trying to identify the
rows that do not contain a duplicate amount. For example I have rows with

100
100
97
97
96
96
98
101

I am trying to pull the list of 98 and 101. The spreadsheet is a list of
invoices paid vs invoices unpaid if it is paid there are two rows with the
same data if it is unpaid there is only one row. I am trying to find the
unpaid invoices.

 

[Paul Robinson]

Hi
try searching this group with your header!

"finding unique values and removing all duplicates "

regards
Paul

 

[Dave Peterson]

I'd use a helper column and fill it with formulas like:

=countif(a:a,a1)
(this would go in B1)

And drag down.

Then I could apply data|filter|autofilter to column B to see just the rows that
have 1 in column B.

 

[Nick]

Hi-

There are good ways to do this without a macro, but here is a macro that
should work if you have one row as a header and the data in the consecutive
rows with no empty rows (may work even with empty rows). The data will need
to be in columns A, B, C, and D. This will put the word “Match†in column D
of the row that has matching data between the four columns.

Here’s the code:
Option Explicit
Dim X As Integer
Dim Amounta() As Variant
Dim Amountb() As Variant
Dim Amountc() As Variant
Dim Amountd() As Variant
Dim Rows As Integer

Sub FindRows()
X = 1
Rows = ActiveSheet.UsedRange.Rows.Count - 1

ReDim Amounta(1 To Rows)
ReDim Amountb(1 To Rows)
ReDim Amountc(1 To Rows)
ReDim Amountd(1 To Rows)

For X = 1 To Rows

Amounta(X) = Workbooks("finding unique values and removing all
duplicates.xls").Sheets("Sheet1").Range("a1").Offset(X, 0).Value
Amountb(X) = Workbooks("finding unique values and removing all
duplicates.xls").Sheets("Sheet1").Range("b1").Offset(X, 0).Value
Amountc(X) = Workbooks("finding unique values and removing all
duplicates.xls").Sheets("Sheet1").Range("c1").Offset(X, 0).Value
Amountd(X) = Workbooks("finding unique values and removing all
duplicates.xls").Sheets("Sheet1").Range("d1").Offset(X, 0).Value
If Amounta(X) = Amountb(X) And Amountb(X) = Amountc(X) And Amountc(X) =
Amountd(X) Then Range("e1").Offset(X, 0) = "Match"

Next X
End Sub

 

[PY & Associates]

I have a spread sheet with 4 columns and 900 rows I am trying to identifythe
rows that do not contain a duplicate amount.  For example I have rows with

100
100
97
97
96
96
98  
101

I am trying to pull the list of 98 and 101.  The spreadsheet is a list of
invoices paid vs invoices unpaid if it is paid there are two rows with the
same data if it is unpaid there is only one row.  I am trying to find the
unpaid invoices.

Something like this, assuming data in column B1 down

Option Explicit

Sub m()
Dim i As Integer
Dim lrow As Integer

lrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To lrow
If Cells(i, "B") = Cells(i + 1, "B") Then
i = i + 1
Else
Cells(i, "B").Interior.Color = vbYellow
End If
Next i

End Sub