SHARE
TWEET

Untitled

a guest Jun 27th, 2019 58 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. =ARRAYFORMULA(REGEXREPLACE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF('Raw Data'!AL3:AY8<>"",
  2.  SUBSTITUTE('Raw Data'!AL3:AY8, " ", "♠"), )), , 999^99)), " "), "♠", " "))
  3.      
  4. ={{"Timestamp (mm/dd/yyyy)", "Duplicate", "First Name", "Last Name", "Phone",
  5.   "Email Address", "Zip Code", "Parent/Guardian", "Teacher", "Administrator",
  6.   "Elected Official", "Community Member", "Business Organization", "Non-Profit Organization",
  7.   "Affiliated_School_1", "Affiliated_School_2", "Affiliated_School_3", "Affiliated_School_4",
  8.   "Affiliated_School_5", "Affiliated_School_6", "Affiliated_School_7", "Affiliated_School_8",
  9.   "BeCampusRep"}; {QUERY(ARRAYFORMULA(IF(LEN('Raw Data'!A3:A), {'Raw Data'!C3:G, 'Raw Data'!AC3:AK,
  10.  REGEXREPLACE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF('Raw Data'!AL3:AY<>"",
  11.  SUBSTITUTE('Raw Data'!AL3:AY, " ", "♠"), )), , 999^99)), " "), "♠", " "), IF(
  12.  MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))>0,
  13.  IFERROR(SPLIT(IF(LEN('Raw Data'!A3:A), REPT(" ♣",
  14.  MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))), ), "♣")),
  15.  ARRAY_CONSTRAIN(IFERROR(SPLIT(IF(LEN('Raw Data'!A3:A), REPT(" ♣",
  16.  MIN(8-MMULT(LEN(IF('Raw Data'!AL3:AY<>"", "♦", )), TRANSPOSE(COLUMN('Raw Data'!AL3:AY))^0))), ), "♣")),
  17.  COUNTBLANK('Raw Data'!A1:A)+COUNTA('Raw Data'!A1:A)-1, )), 'Raw Data'!AZ3:AZ}, )), "where Col1 !=''")}}
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top