Prev: Finding all unique rows and excluding all duplicates
Next: merge cells without losing data OR format
From: Waylonk on 3 May 2010 15:44
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.

Any help would be appreciated.
From: Tom Hutchins on 3 May 2010 16:45
Here is one way...

Assuming your invoice numbers are in column A, in the first free column enter
=COUNTIF(A:A,A2)
Copy it down through all rows of data. This formula counts the number of
times each invoice appears in column A.

Now apply an autofilter to the worksheet. Set it to only show records with a
value of 1 in the column you just added. The resulting visible rows are your
unmatched invoices.

To copy that list to another sheet, highlight all the filtered data. Press
F5 (Go to) and select Special >> Visible cells only >> OK. Copy & paste to
another sheet.

Hope this helps,

Hutch

"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.
>
> Any help would be appreciated.
 | 
Pages: 1
Prev: Finding all unique rows and excluding all duplicates
Next: merge cells without losing data OR format