1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by BroadwayDubbing, Oct 25, 2017.

  1. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13
    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

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
  3. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13

    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!
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
  5. David Evans

    David Evans Active Member

    Messages:
    650
    Why do people insist on using Excel as a Word Processor?
  6. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13
    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.
  7. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,913
    Following is standard sledgehammer VBA code. See if it works for you.

    Code (vb):
    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.

    Attached Files:

  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !
    Or as a database software !
    As Excel can't display like Word and
    is at least 50 times slower than Access …
  9. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    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.
    BroadwayDubbing likes this.
  10. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13
    Thanks you very much for your help and time!
  11. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    Late in the day. In the attached is Sheet 2 with a button which triggers the following code:
    Code (vb):
    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.

    Attached Files:

    BroadwayDubbing likes this.
  12. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,948
    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

    Attached Files:

  13. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13
    Thank you very much! I really appreciate!
  14. BroadwayDubbing

    BroadwayDubbing New Member

    Messages:
    13
    Thank you very much, you guys are great!

Share This Page