Results 1 to 3 of 3

Thread: IF AND formula (I think?)

  1. #1

    Question IF AND formula (I think?)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I’m somewhat new to Excel and have been trying to figure out a formula for the past 5 hours. I need to know the formula for finding combinations of duplicate data in ROWS only, not columns. For example, if I have 3 rows and 3 columns of data how can I search for duplicate data by rows. I’ve made a simple example below.

    Row 1: 2a 5c yy
    Row 2: 9a 2a 6n
    Row 3: 5c 2a ts

    In this example, if I needed to know how many times the combination of 2a and 5c appeared, the formula would show 2 times (rows 1 and 3).
    I must admit that I got confused when referencing the Microsoft and other websites that gave examples. None of them worked by finding the data by rows.

    Any help would be greatly appreciated!!!!! I'm using Excel 2010
    Last edited by Monday; 2015-04-25 at 06:33 PM. Reason: realign chart example

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Put below formula in Conditional Formatting.

    =OR(IF(AND($A1="2a",$B1="5c"),1,IF(AND($A1="5c",$B1="2a"),1,0)))
    Attached Files Attached Files

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by sambit View Post
    Put below formula in Conditional Formatting.

    =OR(IF(AND($A1="2a",$B1="5c"),1,IF(AND($A1="5c",$B1="2a"),1,0)))
    What if one or both items in the pair are beyond column B ?

    Monday
    Can you say how many columns you need to search, and whether or not you mean any duplicates or just "5c" and "2a" ?

Tags for this Thread

Posting Permissions

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