+ Reply to Thread
Results 1 to 9 of 9

Compare two ranges

  1. #1
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Compare two ranges

    In K8:O100 there is a range of date

    In R8:V100 there is a range of data

    I need a formula in X8 that will look at R8:O8 and see if there is a row in K8:O100 that matches it. If yes that "Y". If no than "N"

    Then I will put the same formula in X9 and look at R9:O9 and see if there is a row in K8:O100 that matches it. If yes that "Y". If no than "N"


    and so on. I just need the first one and then I can drag it down the colums are equal if they match, meaning if it matches K8 will match R8 and L8 will match S8 and so on

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,688

    Re: Compare two ranges

    I need a formula in X8 that will look at R8:O8 and see if there is a row in K8:O100 that matches it. If yes that "Y". If no than "N"
    =IF(countif($K$8:$O$100, R8 )>0, "Y", "N")
    and copy down - now a Y will appear for each row

    maybe i miss-understand exactly what you need because of this
    Then I will put the same formula in X9 and look at R9:O9 and see if there is a row in K8:O100 that matches it. If yes that "Y". If no than "N"
    perhaps a sample spreadsheet - with expected results also added - so we know how the data is laid out on what result you would like and why
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Re: Compare two ranges

    Here is an example. Column X is where the formula will be. X8 for example, needs to look at each row in K:O to see if it matches R8:V8, if yes than "Y", if no than "N"



    K L M N O P Q R S T U V W X

    8 ABQ 5 James A 11 LAS 3 Sammy F 16 Y
    9 SAV 7 KIle B 12 SAV 7 KIle B 13 N
    10 HHH 8 Henry B 88 ABQ 5 James A 11 Y
    11 LAX 10 Janice S 1 LAS 3 Sammy F 14 N
    12 LAS 3 Sammy F 16 SEA 10 Matt D 95 N
    13 BWI 1 Jimmy D 22
    14 JAX 11 Paul T 14
    15 ORD 8 Dexter Z 8

  4. #4
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Re: Compare two ranges

    That didnt display the way I wrote it. Here is an attached example
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,293

    Re: Compare two ranges

    this would be one way (added a concatenation in col P and col W). Then did a vlookup on those.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  6. #6
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Re: Compare two ranges

    Yes, thank you. What about when it does match, right now it doesnt pull back "Y" in your attachment

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,293

    Re: Compare two ranges

    using the rest, change the formula in col X (my version in col Y) to this...
    =IF(ISNA(VLOOKUP(W5,P:P,1,FALSE)),"N","Y")

  8. #8
    Forum Contributor
    Join Date
    08-07-2013
    Location
    mo
    MS-Off Ver
    Excel 2010
    Posts
    453

    Re: Compare two ranges

    Thank you. Works

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,293

    Re: Compare two ranges

    You're welcome, and thanks for the reputation bump.

+ 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. compare two ranges
    By anilg0001 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-07-2013, 03:20 AM
  2. [SOLVED] Compare two Ranges
    By jbmerrel in forum Excel General
    Replies: 5
    Last Post: 04-06-2012, 03:04 PM
  3. Compare Two Ranges
    By macro_beta in forum Excel General
    Replies: 6
    Last Post: 10-04-2010, 10:10 AM
  4. Excel 2007 : Compare two ranges
    By ccpsc in forum Excel General
    Replies: 2
    Last Post: 09-09-2010, 04:11 AM
  5. compare min and max ranges
    By carylee557 in forum Excel General
    Replies: 2
    Last Post: 11-03-2008, 11:16 PM

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