Summarising Data

guest7119 welcome to the forum.

This uses a helper column in column C to identify and group the Scene numbers. The formula is in C2 and filled down is

Formula:

=IF(COUNTIF($A$1:A2,A2)=1,ROWS($2:3))

Then to group the unique Scene numbers in E2 and filled down until you get blanks.

Formula:

=IFERROR(INDEX(A:A,SMALL(C:C,ROWS($2:2))),"")

Then to get the Cast numbers array enter this formula in F2 fill down and across until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

=IFERROR(SMALL(IF($E2=$A$2:$A$10,$B$2:$B$10),COLUMNS($F:F)),"")