Computer programming gurus, please help

Discussion in 'Archived Threads 2001-2004' started by Lee L, Feb 6, 2003.

  1. Lee L

    Lee L Supporting Actor

    Joined:
    Oct 26, 2000
    Messages:
    868
    Likes Received:
    0
    I need to search the data in several spreadsheets to find matches. They are basically Excel sheets with rows of names and numbers. I need to find a way, preferably automatic, to take the value from cell B1 on the first sheet for example and then compare it to the values in cells B1:B300 on another sheet, then value B2, B3, so on and so on. Then, for all the matches I would like to either return the corresponding values in Cell A1, 2, 3... into another sheet or maybe change the font color of A2:B2 to red or something so I can find the matches.

    In looking through help it seems like the MATCH function is what I need but I'm not sure how to change the font or dump the value to another sheet based the location returned by MATCH. Also, I'm not too sure how to automate this in Excel. The last time I did any programming was PASCAL and FORTRAN in High School, 15 years ago.
     
  2. BrianW

    BrianW Cinematographer

    Joined:
    Jan 30, 1999
    Messages:
    2,563
    Likes Received:
    38
    Real Name:
    Brian
    To change the format, use Conditional Formating under the Format menu. It should be self-explanatory. Click the Font button to change the font based on the cell's value, or based on a binary test on other cells.

    The MATCH functions returns just a number, I believe, not a cell reference, so you'll have to somehow use that to index into the "corresponding cells" to display or change the font of.

    You may be making it more complicated than it really is (or maybe I'm just not understanding the complexity of the problem), but do you want to flag a cell just if a match is made? Or do you want to identify the cell on the other sheet with which a match was found?
     
  3. Ryan Wright

    Ryan Wright Screenwriter

    Joined:
    Jul 30, 2000
    Messages:
    1,875
    Likes Received:
    0
    I don't know if you CAN do this in Excel. Perhaps VBScript?

    I would export the data to a comma delimited text file and parse with Perl, but since you don't know Perl that doesn't help you...
     
  4. Steven K

    Steven K Supporting Actor

    Joined:
    Jan 10, 2000
    Messages:
    830
    Likes Received:
    0
    I agree with Ryan... save the file as a CSV file (Comma Seperated Volume). Of course you will lose all formatting in doing so, but the data will be retained in each cell. Now you can parse the plain ASCII text however you'd like. You don't have to know Perl in order to parse text, but its alot more painful in other languages [​IMG]
     
  5. Andrew Chong

    Andrew Chong Supporting Actor

    Joined:
    May 7, 2002
    Messages:
    739
    Likes Received:
    0
    If you intend to use Visual Basic for Applications, try recording a macro: Start recording, Perform an example of what you want to accomplish as simply and with as few keystrokes as possible, Stop recording.

    Then, view the resulting code and, this is the tough part and will require some expertise, edit the code to accommodate all other cells in that first sheet.
     
  6. Lary Larson

    Lary Larson Stunt Coordinator

    Joined:
    May 3, 1999
    Messages:
    77
    Likes Received:
    0
    Here's a little-known feature of Excel that probably won't help, but maybe you can find a use:

    To find cells unlike a given cell in a single row or column range, first select the range (a row or column or a portion of either), then tab to the value to which you'd like to compare (do nothing if it's the first cell), then press CTRL- if your range is a row, or CTRL-SHIFT- if your range is a column. The different cells will be selected.

    Admittedly, a manual process. If you want to go the VBA route, the key line of code is Selection.ColumnDifferences(ActiveCell).Select. Change ColumnDifferences to RowDifferences as appropriate. You should be able to change the parameter passed to ColumnDifferences to something other than ActiveCell to make a comparison to a value outside the selection range.

    HTH,
    Lary
     
  7. Chad Ellinger

    Chad Ellinger Second Unit

    Joined:
    Jun 18, 2000
    Messages:
    269
    Likes Received:
    0
    You can use the VLOOKUP formula to accomplish what you're trying to do. This formula:

    =VLOOKUP($A1, $B$1:$B$300, 1, FALSE)

    will search the B1:B300 range for the value in cell A1. If it finds the value, it will return the value in A1, otherwise it will return #N/A. Modify the first two arguments of the function to match the sheet and cell ranges you are dealing with. You can use VLOOKUP to apply conditional formatting as well.

    Good luck!
     

Share This Page