With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
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 MIwww.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' ")