-1

I want to be able to copy and paste an order of draw (starts with the draw number and school name, then each proceeding row is the athletes names) which is just a long array in a single column and break it up into multiple columns.

For example, the draw I'm pasting into column G might look like:

1 SCH - SCHOOL NAME

Jane Doe

Anne Johnson

Sarah Peterson

Haley Jones

Elizabeth Taylor

2 SSCH - SECOND SCHOOL NAME

* more names * 

I want to break it up so in the first column, it's the school name, then in the next the first athlete of the first team (1 SCH), then in the next column, the second name, fourth column has the third name, etc. up to 8 names (may have anywhere between 4-8 names per team). Then the next row is the next team. So in the end it would look like:

row 1: SCHOOL NAME Jane Doe Anne Johnson Sarah Peterson Haley Jones Elizabeth Taylor

row 2: SECOND SCHOOL NAME * more names *

1
  • 2
    Welcome to Web Applications Stack Exchange. How can one tell whether a name represents a school rather then an athlete? Do the school names always start with a number? Are they all upper case while athlete names are mixed case? Please show realistic-looking sample data like FOREST PARK MIDDLE SCHOOL instead of 1 SCH - SCHOOLNAME. May 15 at 4:52

1 Answer 1

1

Use scan(), map() and filter(), like this:

=let( 
  data, tocol(A1:A, 1), 
  pattern, "^\d+ +[A-z]+ +- +", 
  schools, scan("", data, lambda(a, c, 
    if(regexmatch(c, pattern), c, a) 
  )), 
  athletes, scan("", data, lambda(a, c, 
    if(regexmatch(c, pattern), iferror(ø), c) 
  )), 
  map(unique(schools), lambda(s, 
    hstack( 
      regexreplace(s, pattern, ""), 
      torow(filter(athletes, schools = s), 1) 
    ) 
  )) 
)

See let(), tocol(), scan(), regexmatch(), map(), unique(), hstack(), regexreplace(), filter(), and torow().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.