-2

I am having trouble with conditional highlighting.

Scenario: I have a list of ID's (A1) each correspond to one Name (B2). For each ID there may be one or more Codes (C2) corresponding to it. Thus, I would like to highlight each ID that has more than one Code listed. In the example snap shot I provided, I would have highlighted Row 5 or cell A5 becuase it has C5, C9, C13, and C24 associated to it. Once I've highlighted row 5 or cell A5 I can filter by cell color to evaluate the values in column E and F.

Please see updated image for reference, the cells are not merged.

Example data

ID  Name    Code    Name    #   Alpha
1234    Doe, Jane   WORK    WORK 1  5017    ABC
                11147   DEF
5678    Doe, John   OUT OUT 1   5014    JKL
                5017    MNO
                11147   PQRS
                16000   TUV
        OPEN    OPEN 1  5010    DEZ
                5017    ABC
                11123   CCC
                16000   TUV
        CLOSED  CLOSED 1    2665    BB
                5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
        LIFT    LIFT 1  5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
                15022   BLUE
8876    Blue, Jean  WORK    WORK 1  5017    ABC
                11147   DEF
                16000   GHI
  • How do you figure that? Also, you may have trouble since you have merged cells. What have you tried so far? – BruceWayne Aug 16 '17 at 22:36
  • The cells are not merged, they just don't have the border style applied. I've not been able to successfully try anything. I was using other similar but not exact question responses without success. – Loren Aug 16 '17 at 22:52
  • 1
    Your explanation is very confusing. "C7 contains more than one code for A5". Huh? A5 contains 5678, which appears nowhere else in the table, and C7 is blank. Please endeavor to clearly explain what you're trying to do. – Bandersnatch Aug 16 '17 at 22:59
  • I've updated my post for more clarity. – Loren Aug 16 '17 at 23:11
  • It would help a lot if your data was in the question in a format to copy/paste into Excel. – NetMage Aug 16 '17 at 23:19
1

FYI having 2 columns titled Name is very confusing.

I can't think of an easy way to highlight the R5 row because B5 has no relationship direct relationship with C9. It would have to be a messy formula that make checks on how many blank cells below. A much easier solution would be to highlight any additional Code entries, ie ones that aren't directly adjacent to a Name: Example conditional formatting

To accomplish this you apply conditional formatting to the columns C and D (you can apply it to the full width of the table if you wish). You choose a highlight rule and Use a formula to determine which cells to format (I'm on a Mac using a different version of Excel, so your dialog box may look slightly different): Conditional Formatting

And you apply the rule:

=AND(ISBLANK($B1),NOT(ISBLANK($C1)))

This says highlight any cell where Bx is blank AND Cx is not blank. The dollar signs are important.

  • Thank you for cleaning up the post and the answer, I removed columns E and F for now, filtered out the blanks in columns C & D and modified your suggested formula to highlight A5 (I'll exclude the A7 highlight): =AND(NOT(ISBLANK($A1)),(ISBLANK($A2)) – Loren Aug 17 '17 at 0:01
0

For my own practice I wanted to see if/how this could be done in its original intent. As expected, it's a horrible mess:

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

I'll break it down piece by piece.

For each value in column B we want to look further down until we find another value. Using the example above if we are looking at B2 we look further down until we see the next value at B5. I can't find a Excel function to "keep searching until it finds something", so we'll always look 50 rows down.

We can generate that range with the formula:

=$B2:INDIRECT("$B"&ROW()+50)

Note: this range doesn't make sense if entered directly into a single cell.
INDIRECT generates a reference to a cell from the text provided.

To find the next populated (not blank) cell we have to work with arrays. To save space I'll hardcode in a range for the time being. I used example code from this page:

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52))

Note that when entering formulas containing arrays into worksheet cells you must use CTRL-SHIFT-ENTER (not necessary for conditional formatting formula entry). Normal ENTER will produce different results and cause you to tear out hair. Take my word for it.

So find all cells in the provided range and check if they are not blank. If any are not blank, return the minimum row number of that element. Except that will include B5, and we actually want to look 1 cell back, so ROW - 1:

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52) - 1)

Except of course our real formula is now:

=MIN(IF(NOT(ISBLANK(B2:INDIRECT("$B"&ROW()+50)))), ROW(B2:INDIRECT("$B"&ROW()+50)) - 1)

Then we need to check if there are more than one value in Column C in the range C2 to C4. This clever code checks if all cells in the range are blank:

=SUMPRODUCT(--(range<>""))=0

range<>"" checks if each cell in the range is not equal to "" (empty). This will return an array of boolean values like:

{TRUE,FALSE,TRUE}

The double hyphen then converts the TRUE FALSE values to one's and zeros:

{1,0,1}

Where 1's in this array correspond to cells that are not blank or empty. SUMPRODUCT adds (sums) all the elements in the array, if your final result is not zero, there were populated cells.

Of course we expect one value in C2 that corresponds to the entry in B2, so we expect 1 value. We change it to this:

=SUMPRODUCT(--(range<>""))>1

Tying it all together we get:

=SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1

EXCEPT this will return true for more than just the rows containing a name/ID. So we need to wrap it in some additional checks:

=AND(NOT(ISBLANK($B2)), the rest)

And THAT is your final formula, giving TRUE or FALSE as expected:

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

EXCEPT CONDITIONAL FORMATTING DOESN'T ACCEPT IT Invalid conditional formatting

I had to add another column (which could be hidden) to store the result, and base the conditional formatting on that. Final result

References:
INDIRECT
ROW
MIN
IF
NOT
ISBLANK
SUMPRODUCT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.