Excel: Combining IF and duplicate value

[Florent_be]

Hello everyone,

First of all, I'm just starting with excel so my question will probably look very stupid or basic, and, in advance I apologize, but I'm really struggling and this excel will be super important kind of follow up file.

So the situation:

I have two teams that have to work on data.

Theses data are controlled by two numbers, e.g. 1111/00, 1111/10, etc... they are in two different columns.

In my work, there is some risk that the two teams work on the same data at the same time, and I have to avoid it.

So I will import these control numbers from two different excel sheet into one (4 columns as on the example screenshot).

On the screenshot, we can't separate the column A and B or D and E.

My goal is to highlight the common sets of values (yellow lines in the example, that is highlighted by hand without any function). I would like something like IF on of the value A and B = one of the value D and E, then, highlight the 4 cells in red (or show WARNING).

Thank you so much for your help

Have a nice day :)

Florent

[#2]

@Florent_be 

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

[#3]

@nabilmourad 

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

[#4 · Accepted solution]

@Florent_be 

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

[#5]

@Sergei Baklan 

Hello Sergei,

Thanks for your answer, that's exactly what I wanted!!!!

Again, thank you!!!!

Flo

[#6]

@Florent_be 

Personally I would up the technology level to achieved improved functionality.  Both Team1 and Team2 data could be turned into tables in order to make all references dynamic.  The highlighting formulas may be developed using worksheet cells but ultimately Name Manager should be used to provide names for the formulas.  Thus, 'highlight1' (used for the table Team1) should refer to

= COUNTIFS(Team2[A],Team1[@A],Team2[B],Team1[@B])

whilst the corresponding 'highlight2' would be

= COUNTIFS(Team1[A],Team2[@A],Team1[B],Team2[@B])

These named formulae are then used as the basis for conditional formatting.

[#7]

Flo, you are welcome