Prev: Sorting in Pivot table
Next: Finding all unique rows and excluding all duplicates
From: waylonk on 3 May 2010 15:42
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.

From: Iriemon on 3 May 2010 15:47
One simple method would be to sort the worksheet by your primary data and
then put:

=if(a2=a3,"X","")

in a helper column. All parts with blanks would be non-duplicates

"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.
>
From: Lars-�ke Aspelin on 3 May 2010 16:40
On Mon, 3 May 2010 12:42:55 -0700, waylonk
<waylonk(a)discussions.microsoft.com> 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.


If the above numbers are in column A from row 1 to row 900, try the
following formula in cell E1:

=IF(COUNTIF(A$1:A$900,A1)=1,"X","")

Copy this down column E to row 900.
There will be an "X" on those line with a unique number.

Hope this helps / Lars-�ke
 | 
Pages: 1
Prev: Sorting in Pivot table
Next: Finding all unique rows and excluding all duplicates