yogi_Compare Lists In Two Different Sheets And Extract in Third Sheet Specified Field Values For Unique Records Only

                                          Google Spreadsheet   Post  #938

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 25, 2012
user Gilles-Japon said:(http://productforums.google.com/forum/?zx=cmk5k2pk0bbf#!category-topic/docs/spreadsheets/_4dT8rzAKG8)
Compare information from two sheets
Hello,

I have two sheets with information arranged differently. 

The first sheet that is sent to me regularly shows one person's information per line, including a unique ID.

The second sheet takes the same information (including unique ID's) and is arranged by family units per row, placing spouses and children in separate columns.

I want to be able to identify quickly the changes made, whenever a new updated sheet is sent to me. It could be that people have been removed from the first sheet, or people have been added.

I therefore need a double check with the results on a third sheet.

First check, looking at column A from the first sheet (one unique ID per row) for the ID and searching in several columns in sheet 2 for their matching field.

The second one, looking at several columns in sheet 2 for the unique ID's and comparing it to the first sheet's column A (where the unique ID are stored).

I have made a query, based on Yogi's blog (super resource) to match the first condition (look into sheet 1 for each unique ID and compare to 6 different columns in sheet 2), but fail to make the second query properly.

Here is the query I could make : 

=query(index('Ilot484-11-2012'!A2:A&"");"select Col1 where not '"&join(";";'Sur MailChimp'!G2:G)&join(";";'Sur MailChimp'!N2:N)&join(";";'Sur MailChimp'!S2:S)&join(";";'Sur MailChimp'!V2:V)&join(";";'Sur MailChimp'!Y2:Y)&join(";";'Sur MailChimp'!AB2:AB)&"' contains Col1 label Col1 'Numics dans Ilot484-11-2012 qui ne sont pas dans Sur MailChimp' ")

The sample sheet can be found at : https://docs.google.com/spreadsheet/ccc?key=0AkGTeIoz4AcxdDdDZjdjdWtfaU96eHEwaGx0SEFSVkE

Any idea on how to proceed ?

Thank you in advance for all your help.

Gilles

----------------------------------------------------------------------------------------------

following is a solution to the problem