How Do I Identify 1 to 1 Offsetting Entries in a List?
 

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!

How Do I Identify 1 to 1 Offsetting Entries in a List?
 

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.

" 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!

How Do I Identify 1 to 1 Offsetting Entries in a List?
 

Worked perfectly, thank you!

How Do I Identify 1 to 1 Offsetting Entries in a List?
 

Glad to hear that. You're quite welcome.

" wrote:


Worked perfectly, thank you!