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:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

I just noticed the X1SD in the pic above.

We're going to need an Excel workbook uploaded in order to see all of this in context. Please no more pics or screen shots. They are not possible to edit or test formulas in, and no one wants to re-type data you already have. I hope you understand.

If you are not familiar with how to do this:

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• be sure to desensitize the data

The file name will appear at the bottom of your reply.

Edit Check my next post.

Thanks for the upload.

I interpreted the mission wrong the last time.

This has two helper columns. These will return the row and column numbers from the source data. They must both be array entered (Ctrl + Shift + Enter). In M2 and filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Array entered in N2 and filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

Then the final output formulas in will return the Scene and Cast No. These are regular formulas and can be committed with Enter. In O2 filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

In P2 filled down until you get blanks.

Formula:

Please Login or Register  to view this content.

That second helper formula doesn't have to be array entered. I over complicated things. In N2 this regular formula filled down until you get blanks.

Formula:

Please Login or Register  to view this content.