Parsing out words from a cell

[roscoe]

I have a movie database that exports the genre categories for each movie as a list within a single cell, separated by "space hyphen space" (See examples below). I'd like to generate a list of movies in each category but haven't a clue where to start. A brute force method is using the quick filter, but for that I'd at least need a list of all the possibilities in the "Genre" column, but I'm not even sure how to do that. Note the Genre column is not the last column or a convert-text-to-columns would be a good start

As I have over 1100 movies (and growing) in my database this is not a task for manual effort.

Thoughts? ideas?

Thanks

(examples)
Thriller - Action - Comedy - Crime - Drama
Comedy - Romance
Comedy - Drama - Science Fiction
Adventure - Action - Thriller - Science Fiction
Crime - Thriller - Drama
Action - Thriller - War
Science Fiction - Thriller - Romance
Action - Adventure - Romance

 

[Rick Rothstein]

Select the column, press CTRL+H and replace " - " (space-hyphen-space) with a character that you know won't be in the cell (maybe the vertical bar | symbol), then use Text To Columns using that character as the delimiter.

 

[roscoe]

I got that, thanks (sorry if I wasn't clear)...the question is how do I collate several columns of info into a unique list in an automated fashion?

 

[Rick Rothstein]

I got that, thanks (sorry if I wasn't clear)...the question is how do I collate several columns of info into a unique list in an automated fashion?

I think you are going to have to shown us a small representative sample of what your data looks like originally and what you want the output to look like afterwards (and where you want that output to be placed as well).

 

[Kenneth Hobson]

I thought it was one column?

I show one column A by range "r" commented and two columns, A and B, for r.

Sub Main()
  Dim r As Range, c As Range, i As Long, j As Integer, s, a
  ReDim a(1 To Rows.Count)
  'Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
  Set r = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  For Each c In r
    If c.Value <> "" Then
      s = Split(c.Value, " - ")
      For j = 0 To UBound(s)
        i = i + 1
        a(i) = s(j)
      Next j
    End If
  Next c
  'Reduce array size so unique and sort works faster.
  a = Application.Index(a, Evaluate("row(1:" & i & ")"))
  a = UniqueArrayByDict(a)
  a = ArrayListSort(a)
  a = Application.Transpose(a)
  [C2].Resize(UBound(a)).Value = a
  Columns("C").AutoFit
End Sub

'Base 1 array returned.
Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
  Dim cl
  With CreateObject("System.Collections.ArrayList")
    For Each cl In sn
      .Add cl
    Next
    .Sort 'Sort ascendending
    If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
    ArrayListSort = .Toarray()
  End With
End Function

'Base 0 array returned.
Function UniqueArrayByDict(Array1d As Variant, Optional compareMethod As Integer = 0) As Variant
  Dim dic As Object 'Late Binding method - Requires no Reference
  Set dic = CreateObject("Scripting.Dictionary")  'Late or Early Binding method
  'Dim dic As Dictionary     'Early Binding method
  'Set dic = New Dictionary  'Early Binding Method
  Dim e As Variant
  dic.CompareMode = compareMethod
  'BinaryCompare=0
  'TextCompare=1
  'DatabaseCompare=2
  For Each e In Array1d
    If Not dic.Exists(e) Then dic.Add e, Nothing
  Next e
  UniqueArrayByDict = dic.Keys
End Function

 

[roscoe]

The database (csv) is 15 columns wide. The Genre column is the third column in. A copy can be found here (https://www.dropbox.com/s/l4d0vmhheur5e4k/Movies.csv?dl=0). Note the program that generates the file is fixed so I can't change the output

I"m going to apologize...I'm using Excel for Mac without macro support (It was in my original post but inadvertently deleted during a rewrite). I also have Excel 2007 for windows but don't use it often as it's in a virtual windows machine and a pain to work with...but I can if it's the best solution.

As for the output, at this time I really don't care. It can be a separate file or a separate tab. I'm just looking to provide a list of movies (Column 1) for each Genre. My thought would to be a simple filter based on "text contains" and cycle through each Genres (copying the results to a separate tab) but that is labor intensive. An automated solution would be so much better.

 

[roscoe]

So I gave up on this for a while, re-engaging.

I stumbled across some tips that allowed me to split the genres from a single column (comma delimited) to multiple columns per movie. For example, if column "S" was "Adventure,Action,Thriller,Science Fiction" then I was able (by formula) create (to the right of the table) 4 adjacent columns with "Adventure" "Action" "Thriller" and "Science Fiction, respectively. Problem 1 solved.

So now I have a contiguous series of columns with either text or blanks (not all movies have the same numbers of assigned genres).

Next step is to take these columns (currently 7 but if I add genres to individual movies that number could grow) by a continually growing number of rows (movies) of random words and collect them into a single unique list by formula (array formula??)

Any clue how this can be done? Again, recall that my home version of Excel doesn't run macros. I tried a newer version but the custom sort I built in the older version crashes the newer version (Man how I hate the Mac implementation of Office products...)

Thanks!