+ Reply to Thread
Results 1 to 6 of 6

Selecting cells with specific conditions

  1. #1
    Registered User
    Join Date
    07-08-2008
    Location
    Greensboro
    Posts
    4

    Selecting cells with specific conditions

    I am having trouble finding a function that will allow me to select cells that meet certain conditions across an entire sheet instead of just a row or column. I have an identity matrix with cells that contain numeric values that range from 0-1 and I need to select all the cells which contain a value greater that or equal to .975

    Autofilter can accomplish this task, but only for one column at at time. As I routinely need to check matrices with 50+ columns, I need a quicker solution. I think I could do this with Visual Basic, but I have little programming experience. Could anyone help me out?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    What do you mean by "select"? Do you just want to highlight those cells, or filter rows that may contain those numbers in one or more columns?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    07-08-2008
    Location
    Greensboro
    Posts
    4
    I want to highlight those cells. Sorry for the confusion. Basically I need to bold all the cells that are greater than .975 in value. After that I have to cross reference all the bolded cells in a column with their respective file names. I will attach a file, which contains some of the information I have to work with. I realize that someone with the proper training could automate the entire process, but I sadly lack that skill. The filenames are listed in column A and the rest of the data is a matrix. So column B stands for File 1A compared to every other file in the matrix
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-08-2008
    Location
    Greensboro
    Posts
    4
    I am not sure that my last post made sense to anyone but me. Let me explain the end result I wish to obtain for those who cannot decipher my unfortunately jumbled thought process.

    These numbers represent the level of similarity between the files listed in column A. I need to organize all the files into groups which all share at least .975 identity. So continuing the bolded cell method that I am currently using, all the cells in a column which are bold would be grouped.

    Also since the data is in an identity matrix, only the bottom "half" or the top "half" of the data is relevant since theoretically they should be a mirror images of each other.

    I hope that is clearer.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Well,

    Not sure if I got you... but.. perhaps this will help?

    First to highlight all values greater than 0.975, select columns B to BB and go to Format|Conditional Formatting...

    Select Formula Is from 1st drop down and enter formula: =AND(ISNUMBER(B1),B1>0.975)

    Click Format and choose Bold from Font tab....

    Click Ok... click Ok again to finish.

    To filter out rows that contain these bolded numbers use a helper column...

    In BC2, enter formula; =MAX(B2:BB2)>0.975 and copy down.

    TRUE indicates that there is at lease one number larger than 0.975 in that row...

    Now you can re-filter the columns and filter for TRUE in column BC

    I hope I understand your needs...

  6. #6
    Registered User
    Join Date
    07-08-2008
    Location
    Greensboro
    Posts
    4

    Smile

    Wow that is amazing. You are awesome! Of course now that you told me how to do it, it seems like I should have known that myself. You have just saved at least 20 hours of mind numbing clicking. I hope karma is good to you.

+ 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