Tuesday, August 5, 2014

Pull Data From 'Master Roster' Names Of Students (arranged FirstName LastName) With Pass in column D

                          Google Spreadsheet   Post  #1721
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug-05-2014
post by gregpearl:
(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/dyJOwWMaA3g)
Reformatting a column of names inside a QUERY/IMPORTRANGE formula
I'm using a QUERY(IMPORTRANGE formula to import a column of names, which are constructed as "Lastname, Firstname." I would like to reconstruct the names so they appear after import as "Firstname Lastname."

I've tried the very ugly...

=ARRAYFORMULA(ARRAYFORMULA(IFerror(REGEXEXTRACT(query(IMPORTRANGE("spreadsheet key", "sheet!A2:C") , "Select Col1 where Col3='True'"),",\s(\w+)")))) & " " &(ARRAYFORMULA(IFerror(REGEXEXTRACT(query(IMPORTRANGE("spreadsheet key", "sheet!A2:C") , "Select Col1 where Col3='True'"),"(\w+),\s")))) 

...but it only provides one cell of data rather than the entire range imported. Is there a way to combine my two regular expressions into one REGEXEXTRACT formula to achieve the results I'm after? Or perhaps there is an even easier approach I am simply overlooking.

In either case, thank you in advance to anyone who may be able to help!

G
---
Sure thing.

https://docs.google.com/spreadsheets/d/1OKhjKutT1YZuGTl2-rWJf9tNvc8aXU-ETtWeAtsVZO0/edit?usp=sharing

I've limited this example to just one workbook for simplicity's sake...but my use of the IMPORTRANGE function on the "Passing Students" sheet is meant to demonstrate that the same result could be achieved if the "Master Roster" data was in a separate workbook.

Given my previous explanations of what I was trying to accomplish, hopefully this example provides further clarity. If not, though, please don't hesitate to press me for details. What's more, if you find my solution clunky or obtuse, please feel free to offer tips for improvement and/or elegance!

Greg
-------------------------------------------------------------------------------------------------------------------------------------------------