Saturday, July 21, 2012

yogi_Merge Data From Sheets With Different Column Layout Into a MasterSheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #644   Jul 21, 2012     www.energyefficientbuild.com.


user ianjastreb said:(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4KBIbp5knJ8)
Can you query data using column names ?

Hi Gurus !!
I am looking for a way to query data based on the column name.
I have multiple tabs (see tab 1 and 2 below) and a cumulative totals tab (see tab3 below).
Does anyone know how to automate this? Query / index / match ???
Tab1
                      Ben David  Jon Bryan
5/30/2012 10:00:00
5/31/2012 10:00:00 2 2 1 4
6/1/2012 10:00:00 1 3
6/4/2012 10:00:00 4 1 1 1
6/5/2012 10:00:00 1 7 2
6/6/2012 10:00:00 2 5 3
6/7/2012 10:00:00 1 5
6/8/2012 10:00:00 1
Tab2
                      Ben  Bryan Dan  David
5/30/2012 10:00:00 7 3 3 9
5/31/2012 10:00:00 5 5 4 6
6/1/2012 10:00:00 2 5 2
6/2/2012 10:00:00 4 7 4 6
Tab3
                     Ben  David   Jon  Bryan Dan
5/30/2012 10:00:00 7 9 3 3
5/31/2012 10:00:00 7 8 1 9 4
6/1/2012 10:00:00 0 2 1 5 5
6/2/2012 10:00:00 4 6 7 4
6/3/2012 10:00:00 0 0 0 0
6/4/2012 10:00:00 4 1 1 1 0
6/5/2012 10:00:00 1 7 2 0
6/6/2012 10:00:00 2 5 3 0
6/7/2012 10:00:00 1 5 0
Thanks,
Ian
----------------------------------------------------------------------------------------------------
following is one way where 
1)I have rearranged the data from the source sheets into Sheet1a, and Sheet1b so that I have the same number of columns and the columns are in the same order in the rearranged sheets
2) then I merged the data from the rearranged sheets into a MasterSheet ordered by Timestamp column in Ascending order