Categorising all instances of a text value

[brennfalcon]

Hello and Merry Christmas

I have a spreadsheet with about 14,000 rows of text data, much of which still needs a lot of cleansing but I'm sure there must be a number of ways to save time. One of the tasks I need to do is to be able to sort all the entries in one column, theatres, into one of seven categories. Some theatres will show up once or twice, others tens of times. E.G.

Grand Theatre A
Grand Theatre A
Grand Theatre B
Brown Theatre
Green Theatre

These then need to be sorted and categorised into (in another column), for example, Huge Theatre, Medium Theatre and Small Theatre so:

Grand Theatre A - Huge Theatre
Grand Theatre A - Huge Theatre
Grand Theatre B - Huge Theatre
Brown Theatre - Medium Theatre
Green Theatre - Small Theatre

I need a means to first extract a list of unique entries for the Theatres so that I can then categorise them 'manually' and then apply it to the rest of the entries. I realise I can achieve a lot of this just by sorting etc but wonder if there's a more efficient way? I need to do this with a number of different columns of data in the sheet.

Thanks in advance

 

[Fluff]

How about

Sub GetUniques()

   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("[COLOR=#0000ff]A[/COLOR]2", Range("[COLOR=#0000ff]A"[/COLOR] & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      Sheets("[COLOR=#ff0000]Temp[/COLOR]").Range("A2").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

This will get all the unique values in col A of the active sheet and output them to a sheet called Temp.

 

[brennfalcon]

I've started using Excel again after a break of a number of years and was never really familiar with macros/scripts etc then. What specific bits of your code do I need to change to make it fit my workbook?

Thanks

 

[brennfalcon]

Hi,

Thanks. I'm returning to Excel after a number of years away and I was never really familiar with scripts etc. What specific bits of your code do I need to change to make it fit my sheet?

Thanks

 

[Fluff]

Change the sheet name in red to match your new sheet, Change the values in blue to match your column