Identifiying differences between colums

[willia09]

I've got 2 separate Excel files with a monthly listing of client Stock Portfolio choices. I want to be able to compare the two separate lists and have excel identify the changes between different months (outputs).

I can't use a pivot table because its stock symbols (so no numbers).....any ideas?

Also, if you know how to incorporate into Misc. Access that would be helpful as well.

 

[General Ledger]

Sounds like a job for an array formula.

If you have a single-column range name Data, the following array formula returns a list of the unique items in the range:

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data)))))

Remember that at the end of typing an array formula, you do not use just ENTER. You need to end with CTRL+SHIFT+ENTER. You will know if you did it correctly by reveiwing the cell containing the formula. The formula will be enclosed in { }

Happy Holidays,

General Ledger

 

[jeffreybrown]

Sorry mine won't work

 

[Bill Rockenbach]

You could run this code. Change names to suite

Option Explicit
Sub CompareColumns()
    Dim c As Range
    For Each c In Range("A1", Range("A65536").End(xlUp))
        If c <> Workbooks("Book3").Sheets("Sheet1").Range(c.Address) Then
            c.Interior.ColorIndex = 36
            Workbooks("Book3").Sheets("Sheet1").Range(c.Address).Interior.ColorIndex = 36
        End If
    Next c
End Sub