If range

  • Thread starter Thread starter pgarcia
  • Start date Start date
P

pgarcia

Hello all,
I have set of data runing from A-C. A and B have dups and C does not, but C
has a differect dollar amount for the dup. I need to high-li the large of the
two (or 3 or 5) amounts. For the first part I have the folling:
=IF(OR(A3=A2,A3=A4),"Duplicate","") and that works. Thank
Data
ATLAKLExpress AKL 5.01
ATLBHXExpress BHX 3.47
ATLBKKExpress BKK 10.20
ATLBRUExpress Duplicate BRU 3.47< -- High-li
ATLBRUExpress Duplicate BRU 1.55
ATLCPHExpress CPH 3.78
ATLDUBExpress DUB 4.20
ATLHAJExpress HAJ 1.54
ATLHKGExpress HKG 1.26
ATLHYDExpress HYD 1.55
ATLICNExpress ICN 1.52
ATLJNBExpress JNB 2.90
ATLLHRExpress LHR 1.92
ATLPLHExpress Duplicate PLH 1.92
ATLPLHExpress Duplicate PLH 3.38<-high-li
ATLPVGExpress Duplicate PVG 2.41
ATLPVGExpress Duplicate PVG 2.41< not sure here
 
If you data is in Columns A - C put this formula in D1 and copy down the row
for testing. You can add the same formula into a conditional format if it
works. thsi is a formula array and you need to put in Shft-CNTL-ENTER to get
the {} around the formula.

=IF(AND(C1=MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17),SUMPRODUCT(--(A1=A$1:A$17)*--(B1=B$1:B$17))>1),TRUE,FALSE)
 
Cool.
Ok, how does this work. Please.

Joel said:
If you data is in Columns A - C put this formula in D1 and copy down the row
for testing. You can add the same formula into a conditional format if it
works. thsi is a formula array and you need to put in Shft-CNTL-ENTER to get
the {} around the formula.

=IF(AND(C1=MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17),SUMPRODUCT(--(A1=A$1:A$17)*--(B1=B$1:B$17))>1),TRUE,FALSE)
Click to expand...
 
what this part of the function does is to create an array of true's and
false's. 17 of them for 17 rows
(A1=A$1:A$17)

Adding the -- converts a true to a 1, and a false to a 0
--(A1=A$1:A$17)

So I'm comparing the A1 value against all the other column A data

I then did then samed with the column B data

--(B1=B$1:B$17)

I then multiple them together to get an array where when column B and column
B match there will be a 1 and where they don't match a 0

--(A1=A$1:A$17)*--(B1=B$1:B$17)

I then multiply by column C

--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17

So now I have an arrray which contains the C value when columns A & B match
and a 0 where they don't match

Now I use theMAX value to get the largest number of the rows where Column A
and b match

MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17)

And now I compare the max value against the column C data and if it matches
the formula will produce a true

C1=MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17

Now you wanted only the cases where more than one row where Column A and b
matched. So I had to add a filter to look at only the rows where Column A
and B matched. I used a similar formula with SUMPRODUCT.

SUMPRODUCT(--(A1=A$1:A$17)*--(B1=B$1:B$17))>1

then I added an AND for the two cases above

=IF(AND(C1=MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17),SUMPRODUCT(--(A1=A$1:A$17)*--(B1=B$1:B$17))>1),TRUE,FALSE)


I guess I really don't need the IF. the function below is equivalnet.

=AND(C1=MAX(--(A1=A$1:A$17)*--(B1=B$1:B$17)*C$1:C$17),SUMPRODUCT(--(A1=A$1:A$17)*--(B1=B$1:B$17))>1)
 
Back
Top