Saturday, June 16, 2018

yogi_Prepare A Consolidated List Of Certifications Earned From Data In 'Form Responses 1'

Google Spreadsheet   Post  #2462

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jun-16-2018

question by: dlrbestpublic!topic/docs/79stcxty14o;context-place=mydiscussions

How to create an array that omits blanks and writes to one cell?

Need some help please. I am using a google form to input certification into a Google Sheets spreadsheet. The form answers get written into a worksheet named 'Form Responses 1'. That tab and my Master tab have a Member ID column for matching purposes. 

Right now, I have no problem copying the first matching line (using Index/Match) from the Form Responses 1 tab into the Master tab to populate that member's certifications. 

However, as I have a few folks that are not familiar with Excel/Google Sheets, I would like the Google Form to be used for certification updates as well -not just a one time entry per member. For example, on initial entry, Certifications 1 and 2 were recorded for Member 1. Then one month later, Member 1 received Certification 3. Using the form to input that adds another line to the Form Responses 1 tab for Member 1. I would like to create an array of all entries for Member 1 and for each cell that is not blank, write that information into one single cell on the Master tab. So far, I cannot figure out how to get it to disregard blanks and to write to the same cell. This is the base formula I have been working with: 

Column B has the Member ID in the one sheet and A3 has it in the other. Column J is just one of the 28 certifications that are being tracked. Let's call that Cert1. I am trying to write the responses found in J (knowing that all will be blank but 1) into cell I3 on the master sheet. I have tried using ISTEXT, <>"", isblank, but the answer is eluding me. Any help is most appreciated.

=ArrayFormula(IFERROR(INDEX('Form Responses 1'!J$2:J$999,If('Form Responses 1'!J$2:J$999<>"",(SMALL(IF('Form Responses 1'!$B$2:$B$999=$A3,ROW('Form Responses 1'!J$2:J$999)-MIN(ROW('Form Responses 1'!J$2:J$999))+1),COLUMNS(I3)))),""))