yogi_Combine The Results Of Six Queries In Sheet Comparer Into A Single Function in Sheet yogi_Comparer

                                          Google Spreadsheet   Post  #943

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 26, 2012
user Gilles-Japon said:(http://productforums.google.com/forum/?zx=potzyfn0cfbg#!category-topic/docs/spreadsheets/5EzIJBtCPik)
Join queries
Hello,

I need to have the results of several queries to appear in a continuous column if possible. I have now 6 independent queries one under the other one. The reason to keep them one under the other, is that the column data are the same for most (ID number, Last name, First name, Spouse name) or can become so (ID Spouse ---> ID, IDchild1 ---> ID, Spouse First Name ---> First Name, Child1 first name ---> First name etc).

One of the problem is that I cannot know how many lines to keep free between queries to allow for the data to be listed without erasing the queries below. 

Here are the various queries :

First query :

=query('Sur MailChimp'!A:AB;"select G, C, E where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains G ")

Second query :

=query('Sur MailChimp'!A:AB;"select N, L, M where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains N ")

Third query :

=query('Sur MailChimp'!A:AB;"select S, C, R where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains S ")

Fourth query :

=query('Sur MailChimp'!A:AB;"select V, C, U where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains V ")

Fifth query :

=query('Sur MailChimp'!A:AB;"select Y, C, X where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains Y ")

Sixth query :

=query('Sur MailChimp'!A:AB;"select AB, C, AA where not '"&join(";";'Ilot484-11-2012'!A$2:A)&"' contains AB ")

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 using VMERGE custom function written by ahab  -- available in script gallery

yogi_Compare Lists In Two Different Sheets And Extract Non_Matching Values From Field A D F Of Sheet ilot484-11-2012

                                          Google Spreadsheet   Post  #939

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 25, 2012
this is an adjunct to the solution in my following blog post

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

http://yogi--anand-consulting.blogspot.com/2012/12/yogicompare-lists-in-two-different.html

which was in response to a question by user Gilles-Japon in the following thread in Google Docs Help forum:

http://productforums.google.com/forum/?zx=xqfo7ic9pzt2#!mydiscussions/docs/_4dT8rzAKG8

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

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