• 551K Members
• 6,597 Online
• 660K Conversations
SOLVED

Highlighted
New Contributor

# Excel: Combining IF and duplicate value

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
Highlighted

# Re: Excel: Combining IF and duplicate value

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

Highlighted

# Re: Excel: Combining IF and duplicate value

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

Highlighted
Solution

# Re: 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

# Re: Excel: Combining IF and duplicate value

Hello Sergei,

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

Again, thank you!!!!

Flo

Highlighted

# Re: Excel: Combining IF and duplicate value

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.

Highlighted

# Re: Excel: Combining IF and duplicate value

Flo, you are welcome

Related Conversations
Continuous page break in Excel on Mac
Henri1010 in Excel on
0 Replies
Data validation
PatrickCarlsen in Excel on
1 Replies
Las gráficas de excel 365 para MacOs no se muestran
Rodo_Cruz in Excel on
0 Replies
Deleted excel files
Rick0302 in Excel on
1 Replies