Monday, December 24, 2012

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    Dec 25, 2012
user Gilles-Japon said:(!category-topic/docs/spreadsheets/_4dT8rzAKG8)
Compare information from two sheets

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' ")
Any idea on how to proceed ?
Thank you in advance for all your help.
following is a solution to the problem