Summarising Data

[guest7119]

Hello All,

Hoping someone can offer up and logical solution. I have a data sample as shown in the attachment.
I need to summarise the data so that I end up with two columns. Column 1 - Scene, and Column 2 - Cast No.

For Example:

Scene Cast No.
Sc. 1.1 1
Sc. 1.2 1
Sc. 1.2 20
Sc. 1.2 21
Sc. 1.2 24
Sc. 1.11 26
Sc. 1.12 1

Obviously this is just a small data set so it is easy to do manually but the actual document has 100's of rows of data.

Thanks in advance.Screen Shot 2017-04-19 at 21.51.59.png

[FlameRetired]

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

=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.

=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.

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

[FlameRetired]

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.

[guest7119]

Thank you FlameRetired. I have uploaded a set of data.

[FlameRetired]

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.

=IFERROR(SMALL(IF($B$2:$K$194<>"",ROW($B$2:$K$194)-MIN(ROW($B$2:$K$194))+1,""),ROW()-ROW($2:$2)+1),"")

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

=IFERROR(SMALL(IF(INDEX($B$2:$K$194,M2,)<>"",COLUMN($B:$K)-MIN(COLUMN($B:$K))+1),COUNTIF($M$2:$M2,$M2)),"")

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.

=IFERROR(INDEX($A$2:$A$194,M2),"")

In P2 filled down until you get blanks.

=IFERROR(INDEX($B$2:$K$194,M2,N2),"")

[FlameRetired]

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.

=COUNTIF($M$2:M2,M2)