#1
|
|||
|
|||
Conditional Formatting results in another column
Hello, I used Conditional Formatting to find values that were in column B but not column C. I now want to show these results (without the shading) of this formatting in column D. In this sample case that would be 3 items. Also, column B may vary up to 1000 different items. Is there a way to rewrite the “Applies to” in the Rules Manager to just the cells in column B that have data starting with B2? I must be using the wrong wording to explain this because I cannot find any solutions. Thank you.
|
#2
|
|||
|
|||
You can do it with a rather long formula
=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,(ROW(B$2: B$12)-ROW($B$2)+1)/ISNA(MATCH($B$2:$B$12,$C$2:$C$12,0)),ROWS($D$22) )),"") |
#3
|
|||
|
|||
Thank you for the help. When I first pasted data into this sheet it did not work correctly and it seems from the formula that this was limited up to the 12th row. My data may be up to 1000 rows so I changed the 12's in the formula to 1000. This created another problem as the empty rows now showed up as 0's. Being that none of my data will ever have a value of zero I just put a IF 0 then "". I imagine there's a better way to fix this but this seems to work fine. Thanks again.
|
#4
|
|||
|
|||
Yes i would just use an if to hide errors or the alike
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to run colour conditional formatting in just column, not whole table | KateWord | Word VBA | 2 | 09-12-2019 05:30 AM |
Conditional Formatting that highlights cells when there was a change in another column | Marcia | Excel | 4 | 04-28-2019 07:34 AM |
How to fill a cell at row and column intersection with conditional Formatting | Iraj | Excel | 10 | 12-02-2017 09:54 AM |
Conditional Formatting a column | Washbue1 | Excel | 3 | 01-27-2017 01:11 PM |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |