Closed Thread
Results 1 to 3 of 3

Conditional formatting - Duplicates in two Spilled array that ignores blanks

  1. #1
    Registered User
    Join Date
    04-09-2025
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    7

    Conditional formatting - Duplicates in two Spilled array that ignores blanks

    Hello,

    I have build a spreadsheet that takes an asset allocation list (Table A) and compares it to the 'Dynamic list' which is filtered based on which asset class is selected from the dropdown at the top of table 'A'. The ''Dynamic List' is a filtered version of the master list, Table 'B'. The asset allocation info is fed from the work sheet tab. The reason for this is not apparent in the sample I uploaded, but it is an important feature for the main working file I will be applying these changes to. I tried several tricks for a countif & match formula for a condition format formula, but I was unable to get anything to work for the entire Table 'A' table. I removed them to clean up the example file. I suspect there is a complication with having Table 'A' as a spilled row and the 'Dynamic list' being a spilled column.

    I would like to fix the conditional formatting currently in place for Table 'A' to highlight asset numbers that match the 'Dynamic List', and changes based on which filter is selected. The built in basic duplicate condition formatting works, but it also highlights blanks which I would like to avoid.

    Can anyone of you fantastic folks help clean up the conditional formatting to only highlight direct matches to the dynamic list and ignore blanks?

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,924

    Re: Conditional formatting - Duplicates in two Spilled array that ignores blanks

    Is this the same as your first thread?

    https://www.excelforum.com/outlook-f...wo-arrays.html
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,924

    Re: Conditional formatting - Duplicates in two Spilled array that ignores blanks

    Administrative Note:

    Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

    Please see Forum Rule #2 about thread duplication.

    I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/outlook-f...wo-arrays.html

    Thread closed.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Referencing Spilled Array to Create Spilled Array
    By truk2 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-30-2023, 11:21 PM
  2. [SOLVED] VBA - Array that ignores zero's and blanks?
    By jsneak in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-24-2019, 06:01 AM
  3. Replies: 1
    Last Post: 08-06-2019, 10:43 PM
  4. [SOLVED] conditional formatting that ignores 'blank' cells
    By jimboryan in forum Excel General
    Replies: 3
    Last Post: 11-14-2018, 07:27 PM
  5. Replies: 5
    Last Post: 05-07-2015, 06:12 AM
  6. [SOLVED] Due dates formula in conditional formatting that ignores blanks
    By ciapul12 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2013, 08:48 AM
  7. [SOLVED] Recording macro totally ignores Conditional Formatting
    By yelsobky in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-24-2012, 05:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1