Hi,
I'm trying to accomplish the following, I have some formulas to detect if there are duplicate values:
I would like to say now: if there is a duplicate value get name from the left of it, in this example it should return "ACS-002 Ind Lait & ACS-005 glaces-ijs". There may be more than 2 duplicate values, so it needs to sum them all. Is this possible? And if so can anyone help me with a formula?
Also looking for a formula that tells me if the duplicate value is the same as the highest number (green cell)
I'm trying to accomplish the following, I have some formulas to detect if there are duplicate values:
202110 - Approve Quotation check v2 -.xlsm | |||||
---|---|---|---|---|---|
AG | AH | AI | |||
30 | Duplicate audittimes? | ||||
31 | Is there a number higher then 0 in 2021? | OK | |||
32 | Is there a duplicate value in 2021? | TRUE | |||
Calculatie |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AH31 | AH31 | =IF(AND($AG$5="0",$AG$6="0",$AG$7="0",$AG$8="0",$AG$9="0",$AG$10="0",$AG$11="0",$AG$12="0",$AG$13="0",$AG$14="0"),"N/A","OK") |
AH32 | AH32 | =IF($AH$31="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0) |
I would like to say now: if there is a duplicate value get name from the left of it, in this example it should return "ACS-002 Ind Lait & ACS-005 glaces-ijs". There may be more than 2 duplicate values, so it needs to sum them all. Is this possible? And if so can anyone help me with a formula?
Also looking for a formula that tells me if the duplicate value is the same as the highest number (green cell)
202110 - Approve Quotation check v3 -.xlsm | |||||
---|---|---|---|---|---|
AF | AG | AH | |||
4 | 2021 | ||||
5 | ACS-002 Ind Lait | 0 | |||
6 | ACS-005 glaces-ijs | 1 | |||
7 | GRMS | 1 | |||
8 | 0 | 0 | |||
9 | 0 | 0 | |||
10 | 0 | 0 | |||
11 | 0 | 0 | |||
12 | 0 | 0 | |||
13 | 0 | 0 | |||
14 | 0 | 0 | |||
15 | 1 | ||||
16 | |||||
17 | Duplicate audittimes? | ||||
18 | Is there a number higher then 0 in 2021? | OK | |||
19 | Is there a duplicate value in 2021? | TRUE | |||
20 | What's duplicate (name)? | ||||
21 | Is duplicate number same as highest number? | ||||
Calculatie |
Cell Formulas | ||
---|---|---|
Range | Formula | |
AF5 | AF5 | =$B$4 |
AF6 | AF6 | =$B$10 |
AF7 | AF7 | =$B$16 |
AF8 | AF8 | =$B$22 |
AF9 | AF9 | =$B$28 |
AF10 | AF10 | =$B$34 |
AF11 | AF11 | =$B$40 |
AF12 | AF12 | =$B$46 |
AF13 | AF13 | =$B$52 |
AF14 | AF14 | =$B$58 |
AG5 | AG5 | =$G$6 |
AG6 | AG6 | =$G$12 |
AG7 | AG7 | =$G$18 |
AG8 | AG8 | =$G$24 |
AG9 | AG9 | =$G$30 |
AG10 | AG10 | =$G$36 |
AG11 | AG11 | =$G$42 |
AG12 | AG12 | =$G$48 |
AG13 | AG13 | =$G$54 |
AG14 | AG14 | =$G$60 |
AG15 | AG15 | =MAX($AG$5,$AG$6,$AG$7,$AG$8,$AG$9,$AG$10,$AG$11,$AG$12,$AG$13,$AG$14) |
AH18 | AH18 | =IF(AND($AG$5="0",$AG$6="0",$AG$7="0",$AG$8="0",$AG$9="0",$AG$10="0",$AG$11="0",$AG$12="0",$AG$13="0",$AG$14="0"),"N/A","OK") |
AH19 | AH19 | =IF($AH$18="OK",SUMPRODUCT((COUNTIF(AG5:AG14,AG5:AG14)-1)*(AG5:AG14<>""))>0) |
Last edited: