Compare 2 excel files

[aksusa]

Guys-


I need your help to compare 2 excel files


Excel File-1 (Sheet1)


Machine Class Instance Parameter RemedyGroup

servername FS /ora FSCapacity ETD

servername1 FS /ora1 FSCapacity ETD1

servername2 FS /ora2 FSCapacity ETD2

servername3 FS /ora FSCapacity ETD

servername FS /ora3 FSCapacity ETD3


Excel File-2 (Sheet1)

Delete Machine Class Instance Parameter RemedyGroup

yes servername1 FS /ora1 FSCapacity ETD1

yes servername FS /ora FSCapacity ETD

yes servername2 FS /ora2 FSCapacity ETD2

yes servername FS /ora FSCapacity ETD


I need to compare Excel File-1 with Excel File-2 for columns Machine | Instance | Remedygroup. For example servername is in Excel File-1 with values of /ora and Remedy Group of ETD and if it is matching then highlight in Green color in Excel File-1 .. if not red color

 

[Luke M]

Helper column to concatenate fields in both files that need to match:

=A2&C2&E2

I'll assume this is col AA for now


Then, in whichever file you want to compare against other list, something like:

=ISNUMBER(MATCH(AA2,[OtherFile.xls]Sheet1!AA:AA,0))


Anything with a TRUE was found in the other list, FALSE indicates no match found.

 

[aksusa]

Is it possible with a VB script

 

[Luke M]

Sure. Quick and dirty, record a macro of you doing the steps I stated above, filter on true/false, color your cells, and delete contents of helper cells.