• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Movie script formatting

BroadwayDubbing

New Member
Hi, my name is Luciano, I work in movie translation and I'm often challenged with script formatting issues, like the one shown. I'd life to merge rows with same contiguous character's names into one single row, so that the text is more readable and less choppy. Of course, to maintain the sense of the dialogue, not all the occurrences of the same character can be merged together, if they alternate with another character. I hope my example clarifies my request.
Thanks!
Luciano
 

Attachments

  • Movie Script Formatting.xlsx
    35 KB · Views: 16
Version of Excel? There are many different solutions, and it may depend on it.

What type of solution are you looking for? Formula based, coding based etc?

I'd recommend PowerQuery method, if you have access to it. If not coding (VBA) based.
 
Version of Excel? There are many different solutions, and it may depend on it.

What type of solution are you looking for? Formula based, coding based etc?

I'd recommend PowerQuery method, if you have access to it. If not coding (VBA) based.


It's Excel for Mac, version 15.37 (170815)
I don't have PowerQuery, but I can download it, if it gets the job done.
Otherwise, either a formula (probably easier) or coding base solution is fine, as long as I can get the results shown in the uploaded sample.
Thanks for your input!
 
Unfortunately, Mac version doesn't have access to PowerQuery.

I'm busy right now, but will see what I come up with, if you don't get help from other members.
 
Why do people insist on using Excel as a Word Processor?

In dubbing, for several reasons: real time computation of actors' compensation based on a 50-charachters-per-line of text, easy filtering of the script by character's name to easily spot dialogue and corresponding timecode, possibility to export tab-separated text files that can be imported as cue-markers in ProTools to expedite recording workflows, and the list goes on.
But if you can advice on how to get the kind of formatting I need on my scripts using Words, by all mean let me know, thanks.
 
Following is standard sledgehammer VBA code. See if it works for you.

Code:
Public Sub ReformatData()
Dim i As Long, j As Long
Dim strActor As String, strDialogue As String

Application.ScreenUpdating = False

j = Range("C" & Rows.Count).End(xlUp).Row + 1
If j > 2 Then Range("C2:D" & j).Delete xlUp

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i).Value <> strActor Then
        If Len(strActor) > 0 Then
            j = Range("C" & Rows.Count).End(xlUp).Row + 1
            Range("C" & j).Value = strActor
            Range("D" & j).Value = strDialogue
        End If
        strActor = Range("A" & i).Value
        strDialogue = Range("B" & i).Value
    Else
        strDialogue = strDialogue & " " & Range("B" & i).Value
    End If
    If i = Range("A" & Rows.Count).End(xlUp).Row Then
        j = Range("C" & Rows.Count).End(xlUp).Row + 1
        Range("C" & j).Value = strActor
        Range("D" & j).Value = strDialogue
    End If
Next i

Application.ScreenUpdating = True

End Sub

I have uploaded the file with code for your reference.
 

Attachments

  • Copy of Movie Script Formatting.xlsm
    13.9 KB · Views: 10
I tend to use Excel over Word any day. ;) I just don't find much value in word processing in general. I use NotePad++ for text editing. Other than that, I haven't opened word processing software in over a year.

I'd imagine reason people use Excel for many things, is it's flexibility and their familiarity with the software. And little to do with appropriateness of the tool for the task at hand. Especially where databases are concerned.
 
Following is standard sledgehammer VBA code. See if it works for you.

Code:
Public Sub ReformatData()
Dim i As Long, j As Long
Dim strActor As String, strDialogue As String

Application.ScreenUpdating = False

j = Range("C" & Rows.Count).End(xlUp).Row + 1
If j > 2 Then Range("C2:D" & j).Delete xlUp

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Range("A" & i).Value <> strActor Then
        If Len(strActor) > 0 Then
            j = Range("C" & Rows.Count).End(xlUp).Row + 1
            Range("C" & j).Value = strActor
            Range("D" & j).Value = strDialogue
        End If
        strActor = Range("A" & i).Value
        strDialogue = Range("B" & i).Value
    Else
        strDialogue = strDialogue & " " & Range("B" & i).Value
    End If
    If i = Range("A" & Rows.Count).End(xlUp).Row Then
        j = Range("C" & Rows.Count).End(xlUp).Row + 1
        Range("C" & j).Value = strActor
        Range("D" & j).Value = strDialogue
    End If
Next i

Application.ScreenUpdating = True

End Sub

I have uploaded the file with code for your reference.

Thanks you very much for your help and time!
 
Late in the day. In the attached is Sheet 2 with a button which triggers the following code:
Code:
Sub blah()
lr = Cells(Rows.Count, "B").End(xlUp).Row
For rw = lr To 2 Step -1
  If Cells(rw, "B").Value = Cells(rw - 1, "B").Value Then
    Cells(rw - 1, "C").Value = Cells(rw - 1, "C").Value & " " & Cells(rw, "C").Value
    Rows(rw).Delete
  End If
Next rw
End Sub
It will need a tweak regarding columns B and C if the data in your real scrpts are not in columns B & C.
 

Attachments

  • Chandoo36194Movie Script Formatting.xlsm
    17.5 KB · Views: 3
Another option using formula with helper

1] C2 helper, copied down :

=IF(A2=A1,C1&" "&B2,B2)

And to hide the words by : select C2:C14 >> Format Cells >> click "Custom", in the type box enter: ;;;

2] E2, copied down :

=IFERROR(INDEX(A$2:A$13,AGGREGATE(15,6,ROW($A$2:$A$13)-ROW($A$1)/(0+($A$2:$A$13<>$A$3:$A$14)=1),ROWS($1:1))),"")

3] F2, copied down :

=IFERROR(INDEX(C$2:C$13,AGGREGATE(15,6,ROW($A$2:$A$13)-ROW($A$1)/(0+($A$2:$A$13<>$A$3:$A$14)=1),ROWS($1:1))),"")

Regards
Bosco
 

Attachments

  • MovieScriptFormat(1).xlsx
    11.7 KB · Views: 10
Late in the day. In the attached is Sheet 2 with a button which triggers the following code:
Code:
Sub blah()
lr = Cells(Rows.Count, "B").End(xlUp).Row
For rw = lr To 2 Step -1
  If Cells(rw, "B").Value = Cells(rw - 1, "B").Value Then
    Cells(rw - 1, "C").Value = Cells(rw - 1, "C").Value & " " & Cells(rw, "C").Value
    Rows(rw).Delete
  End If
Next rw
End Sub
It will need a tweak regarding columns B and C if the data in your real scrpts are not in columns B & C.

Thank you very much! I really appreciate!
 
Another option using formula with helper

1] C2 helper, copied down :

=IF(A2=A1,C1&" "&B2,B2)

And to hide the words by : select C2:C14 >> Format Cells >> click "Custom", in the type box enter: ;;;

2] E2, copied down :

=IFERROR(INDEX(A$2:A$13,AGGREGATE(15,6,ROW($A$2:$A$13)-ROW($A$1)/(0+($A$2:$A$13<>$A$3:$A$14)=1),ROWS($1:1))),"")

3] F2, copied down :

=IFERROR(INDEX(C$2:C$13,AGGREGATE(15,6,ROW($A$2:$A$13)-ROW($A$1)/(0+($A$2:$A$13<>$A$3:$A$14)=1),ROWS($1:1))),"")

Regards
Bosco

Thank you very much, you guys are great!
 
Back
Top