Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If range
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If range
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) "pgarcia" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If range
Cool.
Ok, how does this work. Please. "Joel" wrote: 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) "pgarcia" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If range
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) "pgarcia" wrote: Cool. Ok, how does this work. Please. "Joel" wrote: 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) "pgarcia" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANGE EXCEL copy cell that meets criteria in a range | Excel Worksheet Functions | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |