Forum Discussion
Excel: Combining IF and duplicate value
As a comment, that could be done without helper columns with formula rule for columns A and B as
=SUMPRODUCT(--ISNUMBER(MATCH($A1&$B1,$D$1:$D$100&$E$1:$E$100,0)))*NOT(ISBLANK($A1))
and similar for Team 2
Hi
if you are allowed to create a helper column in column C that combines the values from A & B
So in Cell C2 you type =A2&B2
and another helper column in column F that combines D & E ,
so in F2 you type =D2&E2
Then you drag the 2 calculations all the way down
Then you need to create 2 conditional formatting Rules:
Rule 1>> Select the values in columns D & E >> Home Tab >> Conditional Formatting >> New Rule >> Use Formula To determine which cell to format >> in the Formula box type: =COUNTIF($C$2:$C$5,$D2&$E2)>=1 then >> Hit Format >> Fill Tab >> Yellow >> OK >>Ok
Rule 2>> Select the values in columns A & B >> Home Tab >> Conditional Formatting >> New Rule >> Use Formula To determine which cell to format >> in the Formula box type: =COUNTIF($F$2:$F$5,$A2&$B2)>=1 then >> Hit Format >> Fill Tab >> Yellow >> OK >>Ok
To hide the preparation steps in columns C & F I changed the Font Color to white.
I also explained the 2 rules in the worksheet columns J-K & N-O
Hope that Helps
Nabil Mourad
Thanks mate,
That's great, that's already a huge step, but I still have a problem:
The formula is working great but it doesn't work if I update the list:
- The new data will turn red (I selected red instead of yellow), but the data that was already in there doesn't change
- On the screenshot, the yellow fields are the data that should have changed but didn't.
- On the screenshot, the lines 6 and 7 are new
Thank you so much, your answer helps me so much!!!!
Flo
As a comment, that could be done without helper columns with formula rule for columns A and B as
=SUMPRODUCT(--ISNUMBER(MATCH($A1&$B1,$D$1:$D$100&$E$1:$E$100,0)))*NOT(ISBLANK($A1))
and similar for Team 2
Hello Sergei,
Thanks for your answer, that's exactly what I wanted!!!!
Again, thank you!!!!
Flo
