+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting or formula to identify multiple values...?

  1. #1
    Registered User
    Join Date
    04-06-2016
    Location
    Boston
    MS-Off Ver
    2013
    Posts
    14

    Question Conditional formatting or formula to identify multiple values...?

    Hello, I am wondering if there is a faster/easier way to identify multiple values given a criteria in my data.

    I need all numbers in column C to be the same for any combination of A&B. Any combination of A&B that has more than one number in column C needs to be identified so that I can correct the issue.

    Currently, I am creating a new column (F) & combing the data in columns A, B, & C. From there I am using the "Remove Duplicates" tools to find unique combinations. After this, I am creating another column & extracting the first 11 characters from column F & using Conditional Formatting to find any duplicates. I then use this information to go back & filter my data, etc., to highlight what rows need to be corrected.

    Is there a better way to do this? Either with conditional formatting or a formula that identifies if there are multiple values in column C for any combination of A&B?

    TIA
    Attached Files Attached Files
    Last edited by clairejasper; 02-15-2019 at 09:12 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional formatting or formula to identify multiple values...?

    What if you just conditionally format A2:C12 by Formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Won't this do that?
    Dave

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Conditional formatting or formula to identify multiple values...?

    Here is hopefully a little help. About all I have done is automate the "Remove Duplicates" step that you were doing manually. This is done via the following formula in Column-G:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This gets you to the highlighted duplicate values in column-H with no manual steps being needed. See rows 16 to 26 in the attached workbook.

    I'm sure it's possible to do without the various "helper" columns and combine everything into one formula, but that will need more thought.

    >>>>> EDIT:

    I forgot the attachment. I also needed to change the A/B/C concatenation formula from =A16&B16&C16 to ="T"&A16&B16&C16. Without that the elimination of duplicates did not work correctly when the least significant digits of col-C were changed (Excel working in numbers rather than text I suspect).
    Attached Files Attached Files
    Last edited by GeoffW283; 02-15-2019 at 07:27 PM.

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Conditional formatting or formula to identify multiple values...?

    Here is a formula based attempt. With your raw data in columns A, B, C starting at row-2, copy the following formula into F2 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula outputs "Duplicates" on rows that need your investigation.

    Note that I adjusted your raw data a bit for testing purposes.

    See that attached workbook which implements the above.

    Please test and make sure this meets your needs. Let me know.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-06-2016
    Location
    Boston
    MS-Off Ver
    2013
    Posts
    14

    Re: Conditional formatting or formula to identify multiple values...?

    Thanks for everyone's help! This worked wonderfully. I was even able to use the formula (minus the IF portion) with Conditional Formatting. Thanks so much!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Conditional formatting or formula to identify multiple values...?

    Glad to help - thanks for the feedback and reputation points

+ 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. Replies: 5
    Last Post: 02-11-2019, 03:07 PM
  2. [SOLVED] VBA/Formula to identify similar values in multiple rows and assign a name
    By Rajeshkumar2910 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-07-2017, 05:41 AM
  3. Replies: 3
    Last Post: 10-04-2016, 02:26 AM
  4. Replies: 2
    Last Post: 10-03-2016, 09:57 AM
  5. Quick question: list multiple name values in conditional formatting formula
    By groovybluedog in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-07-2016, 12:00 AM
  6. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  7. Changing Text Format with Formula or Conditional Formatting with multiple values
    By gerodr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2011, 08:54 AM

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