CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Hasan A Yousef
Hasan A Yousef

Reputation: 24988

Split and repeat without

In this sheet, I've the below input data:

enter image description here

As seen, the courses are separated by /

I want to display the same in the format below, where each line shows one course only, with the data of the student repeated:

enter image description here

I know using =split(C3," / ",true,true) can split the courses into 2 columns at the same row, but I need them in the same column, so I tried =TRANSPOSE(split(C3," / ",true,true)) that is working fine for the first line only, but it fail with using ARRAYFORMULA.

Any thought? I'm opened for any potential solution, formula or script or any other.

UPDATE I tried this trick, creating a new column showing number of courses for each student as =ArrayFormula(LEN(REGEXREPLACE(C11:C13, "[^/]", ""))+1)

enter image description here

Then using Rep to repeat each row based on the number of courses =arrayformula({transpose(split(concatenate(rept(B11:B13 & ",",D11:D13)),",",false,true)),transpose(split(concatenate(REPT(C11:C13 & ",",D11:D13)),",",false,true))}) then ended up with:

enter image description here

But here, I've the courses still joint together, how can i split them!

Upvotes: 0

Views: 406

Answers (2)

NightEye
NightEye

Reputation: 11214

You can try this one:

Formula:

=ARRAYFORMULA(TRIM(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(C3:C5, "/"))="",,
 A3:A5&"×"&B3:B5&"×"&SPLIT(C3:C5, "/"))), "×"), 
 "where Col3 is not null")))

Output:

output

Reference:

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9355

I've added two sheets to your sample spreadsheet. "Sheet2" is a cleanup of your testing sheet, "Sheet1." The other sheet ("Erik Help") references Sheet2, not Sheet1, and contains the following formula in cell A1:

=ArrayFormula({"Student ID","Student Name","Course";SUBSTITUTE(SPLIT(QUERY(FLATTEN(SPLIT(FILTER(SUBSTITUTE("/ "&Sheet2!C3:C,"/","/ "&Sheet2!A3:A&"zzz~"&Sheet2!B3:B&"~"),Sheet2!A3:A<>""),"/")),"Select * WHERE Col1 Is Not Null"),"~"),"zzz","")})

This one array formula produces all headers and results.

A virtual array is formed between the curly brackets { }. Headers are introduced first followed by a semicolon, which means "bump down one row to continue." The header titles can be changed as you like.

How It Works:

An addition "/ " is concatenated to the front of every non-blank entry in Sheet2!C2:C. Then SUBSTITUTE replaces every one of these forward slashes with Col A data, "zzz~", Col B data and "~". The tildes (~) will be used later by the outer SPLIT. The "zzz" is added to make sure that ID numbers are converted to text so that they hold formatting throughout the processing and don't turn into real numbers; later, the outer SUBSTITUTE will replace those with null (i.e., get rid of the 'zzz').

Once the initial concatenations are complete, they are SPLIT at the forward slash and then FLATTENed into one column. QUERY removes any blank rows in this virtual array so far. The remaining results are again SPLIT at the tilde. Finally, that outer SUBSTITUTE removes the temporary instances of 'zzz'.

I also added a custom CF formula for the alternating color banding on alternate rows.

Upvotes: 2

Related Questions