Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Identify 1 to 1 Offsetting Entries in a List?
Worked perfectly, thank you! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two worksheets and identify common entries | Excel Discussion (Misc queries) | |||
how to identify unique list of 200 random entries from a list of 3 | Excel Worksheet Functions | |||
Need to identify duplicate entries in a Table | Excel Worksheet Functions | |||
How can you identify duplicate entries in a singe column? | Excel Worksheet Functions | |||
How to identify entries in a matrix also present in another list | Excel Worksheet Functions |