Computer programming gurus, please help (1 Viewer)

[Lee L]

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.

 

[BrianW]

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?

 

[Ryan Wright]

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...

 

[Steven K]

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 :frowning:

 

[Andrew Chong]

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.

 

[Lary Larson]

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

 

[Chad Ellinger]

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!