Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding unique values and removing all duplicates
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding unique values and removing all duplicates
Hi
try searching this group with your header! "finding unique values and removing all duplicates " regards Paul On May 3, 8:39*pm, waylonk wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding unique values and removing all duplicates
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. waylonk wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding unique values and removing all duplicates
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. Heres 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").Offs et(X, 0).Value Amountb(X) = Workbooks("finding unique values and removing all duplicates.xls").Sheets("Sheet1").Range("b1").Offs et(X, 0).Value Amountc(X) = Workbooks("finding unique values and removing all duplicates.xls").Sheets("Sheet1").Range("c1").Offs et(X, 0).Value Amountd(X) = Workbooks("finding unique values and removing all duplicates.xls").Sheets("Sheet1").Range("d1").Offs et(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 "waylonk" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding unique values and removing all duplicates
On May 4, 3:39*am, waylonk wrote:
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding all unique rows and excluding all duplicates | Excel Discussion (Misc queries) | |||
FINDING DUPLICATES - CROSS REEFERENCE 2 COLOUMNS TO FIND UNIQUE EN | Excel Programming | |||
Finding Unique/Duplicates | Excel Worksheet Functions | |||
Duplicates and unique values | Excel Worksheet Functions | |||
check for duplicates, then sum unique values | Excel Discussion (Misc queries) |