06-24-2019 01:49 AM
06-24-2019 01:49 AM
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 :)
06-24-2019 02:30 AM
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
06-24-2019 03:00 AM - edited 06-24-2019 05:14 AM
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!!!!
06-25-2019 01:20 AM - edited 06-25-2019 01:24 AM
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
whilst the corresponding 'highlight2' would be
These named formulae are then used as the basis for conditional formatting.
by jmant37 on May 24, 2020
by Ingeborg Hawighorst on May 13, 2020