Actually you can use conditional formatting to identify both the first item
that is later repeated (with or without same sign) and also identify
that/those later items for to easily pick them out.
Take note of the last cell address in the column, you'll need it for the
conditional formula.
Use the first cell in the column and set up the coditional statements.
Click it and use Format | Conditional Format
In the conditional format window choose the "Formula is" option and enter
this formula (assumes list starts at A2 and extends to A1001)
=COUNTIF(A2:$A$1001,A2)+COUNTIF(A2:$A$1001,-A2)>1
and then set your conditional formatting - this part will provide the format
for numbers that appear later in the column,
Then 'Add' another condition and use this:
=COUNTIF($A$2:A2,A2)+COUNTIF($A$2:A2,-A2)>1
and choose the formatting to identify the duplicated value farther down in
the list.
Then use the format painter to apply the format to all the cells in the
list. (or start out by selecting them all and entering the formulas and
conditional formatting)
It worked with your sample data just fine.
"(E-Mail Removed)" wrote:
> I have a list of 100-200 numbers to reconcile every day, some of these
> offset each other, is there a quick way to identify which do and have
> them highlighted? The example below shows two entries that have
> corresponding offsets, e.g., 1944.09 and 7418.25. The offsets are
> always 1 to 1, meaning there is never a combination of numbers equal to
> one number. Naturally looking for duplicates doesn't work as I am
> comparing positive to negative numbers.
>
> Example:
>
> -1944.09
> 250
> 1944.09
> 125
> 54.69
> 880
> 546.25
> 7418.25
> 6521.58
> 470.45
> -7418.25
> 63.00
> 1115.42
>
> Thanks in advance!
>
>
|
Similar Threads
