Is it possible to use a conditional formatting formula to highlight cells if the following 2 conditions are met:
1. A value is duplicated in one column, and
2. the corresponding cells for the duplicates in another column are not blank (do not need to be equal)

For instance, I have 2 columns: Name and Food. If "John" is listed twice in the "Name" column, and there are values of "apple" and "banana" in the "Food" column, I want them highlighted. I don't care that the food values are different. All I care about is that John is listed twice and has 2 values in the food column.

If on the other hand, Mary is listed twice, but one of the cells in the Food column is blank for her, I don't want those cells highlighted.

John --> apple
Mary --> orange
John --> banana
Mary --> (blank)

I don't know why you guys don't just default to asking this kind of stuff to chatgpt.

Quote:

---

To achieve the conditional formatting you described in Excel, we will use a formula that checks both conditions: (1) if a name is duplicated in the "Name" column, and (2) if the corresponding cells in the "Food" column are not blank.
Suppose your data starts in row 1 with headers, and your "Name" column is A and the "Food" column is B. Here's the formula you can use for conditional formatting:
[ol]

Select the range where you want the conditional formatting to apply. If your data is in rows 2 through 100, for instance, you would select A2:B100.

Go to the Home tab, click on Conditional Formatting, and then choose 'New Rule'.

Select 'Use a formula to determine which cells to format'.

Enter the following formula:

Quote:

---

=AND(COUNTIF($A$2:$A$100, $A2) > 1, $B2 <> "")

---

This formula works as follows:

• COUNTIF($A$2:$A$100, $A2) > 1
  : Checks if the name in column A appears more than once in the range A2:A100.
• $B2 <> ""
  : Ensures that the corresponding cell in column B is not blank.
• AND(...)
  : Combines the two conditions, so formatting is applied only when both are true.

After entering the formula, set the formatting style you prefer (e.g., a specific fill color).

Click OK to apply the formatting.

[/ol]This will highlight the rows where a name is listed more than once and the corresponding cell in the "Food" column is not blank. Remember to adjust the range in the formula (
$A$2:$A$100
) to match the actual range of your data.

---

disclaimer: I have no idea if this is right

Mr President Elect said:

---

disclaimer: I have no idea if this is right

---

It's not. It only checks to see if the value in the first column is listed twice and that something is in the second column for 1 of the duplicate values. I only want it to highlight if both of the duplicate values have something in the next column.

The Dog Lord said:

---

Mr President Elect said:

---

disclaimer: I have no idea if this is right

---

It's not. It only checks to see if the value in the first column is listed twice and that something is in the second column for 1 of the duplicate values. I only want it to highlight if both of the duplicate values have something in the next column.

---

I think I did get it to work using ChatGPT though. Honestly, I constantly forget that it's a thing because I only have a need for something like this occasionally. I also like to try and learn this stuff myself, so my go to is to search for an answer and read about it to understand it. I am glad that ChatGPT provides the explanation for that reason, especially since I couldn't find anything online that was close enough to my prompt this time.

For those that may be curious, the formula and explanation are:

Quote:

---

To achieve conditional formatting that highlights cells in the "Food" column only when a name is duplicated in the "Name" column and there are more than one corresponding value in the "Food" column, you can use the following steps:

Assuming your "Name" column is in Column A and your "Food" column is in Column B:
[ol]

Select the range of cells that contains the "Name" and "Food" columns.

Go to the "Home" tab in Excel, click on "Conditional Formatting," and select "New Rule."

Choose "Use a formula to determine which cells to format."

Enter the following formula for the conditional formatting rule:

[/ol]

=AND(COUNTIF($A$2:$A$100, $A2)>1, COUNTIF($A$2:$A$100, $A2)=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>"))

Replace
$A$2:$A$100
with the range of your "Name" column and
$B$2:$B$100
with the range of your "Food" column.

Explanation of the formula:

• COUNTIF($A$2:$A$100, $A2)>1
  : Checks if the count of occurrences of the value in the current cell of the "Name" column is greater than 1 (i.e., it's a duplicate).
• COUNTIF($A$2:$A$100, $A2)=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>")
  : Ensures that the count of occurrences of the value in the "Name" column matches the count of non-blank values in the corresponding cells of the "Food" column.

[ol]

Choose the formatting style (e.g., fill color) you want to apply to the highlighted cells, then click "OK."

[/ol]This conditional formatting rule will highlight cells in the "Food" column only if the name is duplicated in the "Name" column and there are more than one non-blank value in the corresponding "Food" column. Adjust the range references in the formula to match your actual data range.

---

Quote:

---

You're correct, and I apologize for the oversight. To achieve the highlighting only when both duplicates have non-blank values in the corresponding cells of the second column, we need a slightly different approach. We'll still use COUNTIF to check for duplicates, but we'll also use COUNTIFS to ensure that both corresponding cells in the second column are not blank.


Assuming the same setup (with "Name" in column A and "Food" in column B, starting from row 2), follow these steps:
[ol]

Select the range where you want the conditional formatting to apply (e.g., A2:B100).

Go to the Home tab, click on Conditional Formatting, and then choose 'New Rule'.

Select 'Use a formula to determine which cells to format'.

Enter the following formula:

Quote:

---

=AND(COUNTIF($A$2:$A$100, $A2) > 1, COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>") = COUNTIF($A$2:$A$100, $A2))

---

Explanation:

• COUNTIF($A$2:$A$100, $A2) > 1
  : Checks if the name in column A appears more than once in the range A2:A100.
• COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>")
  : Counts how many times the name appears in column A with a non-blank corresponding cell in column B.
• The
  AND
  function ensures both conditions are met: the name is duplicated, and the number of non-blank corresponding cells in column B equals the number of times the name appears.

After entering the formula, set your desired formatting style.

Click OK to apply the formatting.

[/ol]This will highlight rows where a name is listed more than once and each instance of that name has a non-blank cell in the "Food" column. Adjust the range in the formula to match your actual data range.

---

The Dog Lord said:

---

The Dog Lord said:

---

Mr President Elect said:

---

disclaimer: I have no idea if this is right

---

It's not. It only checks to see if the value in the first column is listed twice and that something is in the second column for 1 of the duplicate values. I only want it to highlight if both of the duplicate values have something in the next column.

---

I think I did get it to work using ChatGPT though. Honestly, I constantly forget that it's a thing because I only have a need for something like this occasionally. I also like to try and learn this stuff myself, so my go to is to search for an answer and read about it to understand it. I am glad that ChatGPT provides the explanation for that reason, especially since I couldn't find anything online that was close enough to my prompt this time.

For those that may be curious, the formula and explanation are:

Quote:

---

To achieve conditional formatting that highlights cells in the "Food" column only when a name is duplicated in the "Name" column and there are more than one corresponding value in the "Food" column, you can use the following steps:

Assuming your "Name" column is in Column A and your "Food" column is in Column B:
[ol]

Select the range of cells that contains the "Name" and "Food" columns.

Go to the "Home" tab in Excel, click on "Conditional Formatting," and select "New Rule."

Choose "Use a formula to determine which cells to format."

Enter the following formula for the conditional formatting rule:

[/ol]

=AND(COUNTIF($A$2:$A$100, $A2)>1, COUNTIF($A$2:$A$100, $A2)=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>"))

Replace
$A$2:$A$100
with the range of your "Name" column and
$B$2:$B$100
with the range of your "Food" column.

Explanation of the formula:

• COUNTIF($A$2:$A$100, $A2)>1
  : Checks if the count of occurrences of the value in the current cell of the "Name" column is greater than 1 (i.e., it's a duplicate).
• COUNTIF($A$2:$A$100, $A2)=COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, "<>")
  : Ensures that the count of occurrences of the value in the "Name" column matches the count of non-blank values in the corresponding cells of the "Food" column.

[ol]

Choose the formatting style (e.g., fill color) you want to apply to the highlighted cells, then click "OK."

[/ol]This conditional formatting rule will highlight cells in the "Food" column only if the name is duplicated in the "Name" column and there are more than one non-blank value in the corresponding "Food" column. Adjust the range references in the formula to match your actual data range.

---

---

Yeah, it is great for learning new things. Honestly, I probably use it more for exploring topics I'm not that familiar with, than having it provide a solution for me. There is a lot of complaints about how it is going to wreck education, but I can't think of how useful this would have been as a study assistant for things like physics of elec & mag to have it be able to really dumb things down to be able to wrap my head around the concepts quicker and have a q&a with.