• 469K Members
• 4,646 Online
• 567K Conversations
SOLVED

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

# 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

# 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

Flo

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,

Again, thank you!!!!

Flo

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies