SOLVED

Excel: Combining IF and duplicate value

Copper Contributor

example.PNG

 

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

 

6 Replies

@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

 

 

example 2.PNG

 

@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

best response confirmed by Florent_be (Copper Contributor)
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

 

@Sergei Baklan 

Hello Sergei,

 

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

 

Again, thank you!!!!

 

Flo

@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.

Flo, you are welcome

1 best response

Accepted Solutions
best response confirmed by Florent_be (Copper Contributor)
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

 

View solution in original post