Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-26-2020, 04:10 PM
14spar15 14spar15 is offline Conditional Formatting results in another column Windows 7 64bit Conditional Formatting results in another column Office 2010 64bit
Advanced Beginner
Conditional Formatting results in another column
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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.
Attached Files
File Type: xlsm Book11.xlsm (19.1 KB, 8 views)
Reply With Quote
  #2  
Old 10-26-2020, 11:11 PM
Purfleet Purfleet is offline Conditional Formatting results in another column Windows 10 Conditional Formatting results in another column Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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) )),"")
Attached Files
File Type: xlsm Book11_Purfleet.xlsm (19.8 KB, 7 views)
Reply With Quote
  #3  
Old 10-27-2020, 10:03 AM
14spar15 14spar15 is offline Conditional Formatting results in another column Windows 7 64bit Conditional Formatting results in another column Office 2010 64bit
Advanced Beginner
Conditional Formatting results in another column
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 10-27-2020, 10:10 AM
Purfleet Purfleet is offline Conditional Formatting results in another column Windows 10 Conditional Formatting results in another column Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Yes i would just use an if to hide errors or the alike
Reply With Quote
Reply

Thread Tools
Display Modes


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 results in another column 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:39 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft