+ Reply to Thread
Results 1 to 6 of 6

List or highlight a cell if it's value repeats in several clusters of data

  1. #1
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    List or highlight a cell if it's value repeats in several clusters of data

    Thank you in advance... please help:

    List or highlight a cell if it's value repeats in ALL clusters of data and do this for ALL cells at once.

    Trust me, this is not the usual "Conditional Formatting"... I've tried that to no avail..


    For example (see attached file, which I hope does get attached):

    I have 6 (six) clusters of data, each one with 70 rows and 11 columns that can have numbers (in this case) or may be blank. It is possible that at a given time, one of the clusters of data may not have any numbers at all.

    I need to find which numbers repeat in ALL CLUSTERS OF DATA (or call them "different ranges of data").

    Cluster 1 range is B2:L71
    Cluster 2 range is B72:L141
    Cluster 3 range is B142:L211
    Cluster 4 range is B212:L281
    Cluster 5 range is B282:L351
    Cluster 6 range is B352:L421


    If 8 repeats in all clusters of data, then highlight it... or put 8 in another cell (column) to show that it repeats in all sections.

    If 3 repeats in all clusters of data, then highlight it... or put 3 in another cell to show that it repeats in all sections.

    If 17 repeats in all clusters of data, then highlight it... or put 17 in another cell to show that it repeats in all sections.

    And so on...


    How to highlight ALL cells that repeat in all clusters at once, or how to list all the cells and it's values on a different range of cells.

    Can this be done with a formula (or array formula) or VB in a way that is automatic without user input once is in place?

    Can you show me in the attached file too?

    For the sake of clarity, here is an image to give an idea... but it does not reflect the actual data, it only illustrates the outcome, what I'm looking for, AND I did't manually highlight all the cells that repeat (but I do want all of the though) because I think it is enough to illustrate the idea.

    Highlightx.png


    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: List or highlight a cell if it's value repeats in several clusters of data

    You may try something like this...

    Select all the ranges and make a new rule for conditional formatting using the formula given below...

    Please Login or Register  to view this content.
    Whereas Cluster1 to Cluster5 are named ranges.

    Refer to the attached for more details.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: List or highlight a cell if it's value repeats in several clusters of data

    Quote Originally Posted by sktneer View Post
    You may try something like this...

    Select all the ranges and make a new rule for conditional formatting using the formula given below...

    Please Login or Register  to view this content.
    Whereas Cluster1 to Cluster5 are named ranges.

    Refer to the attached for more details.

    It works perfectly!!!! It highlights exactly as needed. Thank you.


    How about if not using the highlight option, instead, list those same numbers (using a formula) that repeat in each cluster (range) of data into another area of the worksheet?


    Thank you, thank you so much.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: List or highlight a cell if it's value repeats in several clusters of data

    As to making a list.
    First find the MIN and MAX of the ranges using*:
    =MIN(IFERROR(B$2:L$421*1,100))
    =MAX(IFERROR(B$2:L$421*1,0))
    *Denotes array entered formulas which need to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Note that the MAX is identified first so that the IFERROR value (100) of MIN can be applied.
    A list of numbers between 0 (MIN) and 19 (MAX) is placed in column P.
    A modification of sktneer's formula is then applied to column Q.
    Finally the compressed list of repeat numbers is displayed in column S using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the range N1:Q20 may be moved and/or hidden for aesthetic purposes.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    201

    Re: List or highlight a cell if it's value repeats in several clusters of data

    Thank you JateMc...

    That was the last peace of the puzzle... it works perfectly!!!!!!!!!!!!!

    Clever....

    Have a great day... Cheers.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: List or highlight a cell if it's value repeats in several clusters of data

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cell repeats value above if no data.
    By brent_milne in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-15-2017, 12:34 PM
  2. Macro to return cells if cell repeats value over 5 times in a list
    By hteaford in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2016, 08:36 AM
  3. Trying to check a list of names (with repeats) against another cell value
    By marksman1941 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-24-2015, 12:41 PM
  4. [SOLVED] Repeatedly highlight clusters of cells by adding grids
    By andham in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-13-2014, 06:12 AM
  5. Two Lists Join - But 1st list is index and 2nd list repeats
    By BradCrawford in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-31-2013, 12:48 AM
  6. Replies: 4
    Last Post: 06-01-2013, 04:03 AM
  7. [SOLVED] How can I count the number of repeats in a list of data?
    By SouthCarolina in forum Excel General
    Replies: 7
    Last Post: 03-07-2006, 06:10 PM

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