Excel VBA: Compare lists and find mismatches

[Janisa]

I have an extensive list, with a column to the left contaning a descriptive unique number. In another column (with others inbetween) I have another number connected to the one in the left column.
Frequently I get new list, which may and may not contain changes. I want to check if the update contains any changes compared to the "old" list, and if any I would like Excel to generate a third list containing all the items changed.

 

[SkipVought]

Janisa,

Are you saying that the KEY to determine a MATCH is contained in more than one column?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BrainB]

I would use VLOOKUP().
Say the lists start in cell A1. Sheet1 the old list. sheet2 the new. Checking column B for changes.

In sheet3 A1 put formula =Sheet2!A1
In Sheet3 B1 put formula
=IF(VLOOKUP(Sheet2!A1,Sheet1!A1:$B$100,2,FALSE)<>Sheet2!B1,Sheet2!B1,"")

so the list shows all items contained in sheet2 with changed values - or blank cells if not changed.
Copy/Edit Paste Special/Values to remove formulas, then sort on Column B to get rid of blank cells.




Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[Janisa]

Thanks BrainB!
However, I do not want to display those rows where the lookup has found a match. Only those with mismatches are supposed to be displayed. In other words I would like to perform a check of the list "in the backround" and if any mismatches, display them in a new list. And for this I do believe I need a macro.

 

[Janisa]

To SkipVought
no, the key to determnine match is contained in one column only. If any changes to that column, display the entire row.
If no change, no display at all.

 

[SkipVought]

I think I'd use MS Query via Data/Get External data -- Excel Files -- YOUR WORKBOOK -- YOUR TABLE.

Set up your list to compare on a separate sheet.

Use SQL like this
[tt]
Select M.Identifier, M.......
From MajorTable M LEFT JOIN CompareList C ON M.IDentifier=C.Identifier
Where C.Identifier IsNull
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BrainB]

[However, I do not want to display those rows where the lookup has found a match]

If you try my formula you will see that it does only show mismatches as described in my message.

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================