+ Reply to Thread
Results 1 to 11 of 11

Best compare technique, exact, vlookup? What do I need?

  1. #1
    Registered User
    Join Date
    05-06-2005
    Posts
    8

    Question Best compare technique, exact, vlookup? What do I need?

    Hi,

    I am trying to compare a list with a list in another document. What I need to do is:

    1. look at each cell in sheet 1

    2. compare that 1 cell from sheet 1 with every cell in sheet 2, or at least a range of cells

    3. Indicate by colour or 'true' 'false' that the alpha numeric data in sheet 1 has been found in sheet 2

    Can anyone give help?

    Many thanks,

    F

  2. #2
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    I have tried =OR(EXACT(B5,Sheet1!E2:E29))

    This doesn't always work, and it never works when the range is E:E

    Also tried =IF(J24=VLOOKUP(J24,K24,1),"Yes","No")


    What am I doing wrong?

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Here is two options (see enclosed)
    a) =COUNTIF($D$4:$D$20;F4)>0
    b) =SUMPRODUCT(--($A4&$B4=$A$4:$A$52&$B$4:$B$52))>1

    Hope it helped
    Ola Sandström
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    Thanks,

    How do you do an absolute reference to another sheet? Can you? It doesn't seem to like this style of referencing to another sheet.

    Thanks,

    F

  5. #5
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    Sorted. How can I change the colour of the cells that I have found to match. Probably a macro, but are there any formulas that can change cell colours?

  6. #6
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    To change the cell color (based on a criteria) you have to use Conditional Formatting.
    Here's a lesson on how to use Conditional Formatting: http://www.datapigtechnologies.com/f...rmatexcel.html

    Hope it helps
    Ola Sandström

  7. #7
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    I don't think that will work for what I am attempting to do.

    Here is my formula

    =IF(COUNTIF(Sheet1!$C$2:$N$6200,B4),"Found","Not Found")

    B4 Is the number I am using on sheet 2. I have thousands of numbers on Sheet 1 that I am looking through to find 'B4' on sheet 2. If that number on sheet 2 is found,....I want all of the found ones on sheet one to be hightlighted.


    What do you say?

  8. #8
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Small adjustment to your formula:

    =IF(COUNTIF(Sheet1!$C$2:$N$6200,B4)>0,"Found","Not Found")
    You can then use Conditional Formatting to highlight all "Found", with Cell Value Is: select 'Equal to' and write ="Found" then use Red color.

    Ola Sandström

  9. #9
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    That would not make the "Found" red on the sheet that has thousands of numbers, only on the 'B4'. Conditional formatting doesn't work across sheets

  10. #10
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Sorry. I read badly when trying to cook at the same time.
    Correct. Conditional Formatting doesn't work across Sheets - not even with defined names.
    To make that work you would have to asked the Programming forum for help - VB is not my field of expertice.

    The only other option I can think of is to add a help-column in sheet 1?
    Something like =IF(COUNTIF(...)>0,1,"") Color it white if it shouldn't show....
    Then let Conditional Formatting work against that helper column.

    Maybe somthing
    Ola Sandström

  11. #11
    Registered User
    Join Date
    05-06-2005
    Posts
    8
    Thanks for all you help.

    I was wondering if there is a way that you can print all of those cells that 'Found' applies to and print/ display them together in another area of the sheet? Perhaps this would need a macro?


    Thanks,
    Eric

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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