Please reference my pattern spreadsheet right here: https://docs.google.com/spreadsheets/d/1OsD43iqox_RfatUf-OThryGJ-U9XOo_6e_JzieJIH3o/edit?usp=sharing
Normal Description of Drawback:
I begin with a multicolumn spreadsheet and insert three columns to the left. I need to drop in formulation to the brand new columns to get the output as proven within the instance. The variety of Scenes per Chapter is variable. The variety of Chapters per Act is variable. So these groupings must be constructed from the enter knowledge. There’s a column with a knowledge sort (Scene: f_xxx, Chapter: st_chapter, Act: st_act) and the Title column accommodates constant names for Chapters and Acts, although Scenes can have any identify.
1.) Within the spreadsheet, the three enter columns (D, E, F) symbolize what I’ve to work with.
2.) Columns (A, B, C) symbolize what I need as output.
A.) I can not change the format of the enter columns. They’re produced by a program that I do not management.
B.) Irrelevant to the answer, however only for information, I add new columns to the left as a result of I’ve a operate that copies the unique knowledge (columns) to the correct of the brand new/computed columns.
C.) Clearly Columns ‘A’ and ‘F’ comprise the identical knowledge. It’s irrelevant whether or not column ‘A’ truly will get created.