In article <DA4863FE-6F03-4CBA-96B4-186EBD1F72D0@[EMAIL PROTECTED]
>,
bill_jba <bill_jba@[EMAIL PROTECTED]
> wrote:
> I have a row of data containing 2 inseparable and related columns that I
want
> to compare with all other data rows.
>
> The first column contains an order #, which could contain text and
numbers
> and in the same row, is the second related column, which contains an
amount
> in $.
>
> I want to create a formula to search the data file, which contains more
than
> 5,000 rows, to determine if the same order # and the same amount
together,
> appear in the data more than once.
>
> If the result finds a duplicate, I want the entire row to be highlighted
and
> an separated from the data if possible into a new sheet, with out cut
and
> pasting.
One way:
Select the relevant cells, with the active cell in Row 1
Choose Format/Conditional Formatting...
Change the dropdowns and input box to read
Formula is =SUMPRODUCT(-($A$1:$A$10000=$A1),-($B$1:$B$10000=$B1))>1
Click Format, Patterns, and choose your highlight color. Click OK, OK.
|
