Friday, January 5, 2018

yogi_Match Column Names In Z1:AO1 With E1:Y1 And Pull Data From Matched Columns

Google Spreadsheet   Post  #2342

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-05-2018
Sheet w/input from Form combine like column names into single columns
Could you please offer a solution or ideas to solve this problem? 

 I have a daily sign in form to track attendance  for a community center which has daily classes Mon - Sat. Each day has a different list of classes, but sometimes a different day has the same class as a previous day. So for example:
* Mon has C1, C2, C3
* Tue has C4, C5, C1 & C2
==== In above example C1 and C2 are repeated on Tue but C4 and C5 are unique to TUE but may repeat later in the week.

FORM logic is as follows:
* Select Day (Mon, Tue, Wed, Thu, Fri, Sat)
---- Jump to section of form named for  day selected
---- Data posted to SHEET

Since I have multiple question sections, the data is put into separate column names for each section of the form, sometimes with duplicate event names. Each row only has entries in the columns related to it's section in the Google Form. All other cells are blank for the other sections. 

I'd like to create another set of columns in the sheet, with the names of each class as the first cell in the column, then have a formula following that parses all the cells to the left which contain form class data, and copies the cell contents if it's column name matches the column name of the cell containing the formula. So for example, Cell Z2 looks across cells E2:Y2 (answers begin in column E in this example)  and only copies the contents of a NON-EMPTY cell that matches the formula column name.  There will only be one cell with data in that row matching the formula column name.

This is similar to the problem statement expressed in the link below, except my sections are different but can contain some of the same column (field) names:
https://support.google.com/docs/forum/AAAABuH1jm0s71HlqL04LA/?hl=en

Any ideas? Thanks in advance for the help!

(Copies of example of the Form and Sheet are below, if needed.)



No comments:

Post a Comment