Guest User

Untitled

a guest
Jun 27th, 2019
62
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