How to select a names from a column MS Excel 2007

Question

Hey guys,

I have an Excel document which has a list of students and their group names. I have another sheet within the the same excel document which is called comments. In this sheet, I would like to have a list of individual team names listed.

There are 65 students and 14 defined groups.

Is there a way to select the 14 group names, without repitition?

Cell B3-B67 have the student names. Cell C3-C67 have the team names. The team names are entered against each student.

I know in SQL I could use something like select distinct(team_name) but in Excel, how can I replicate this?

Cheers, Alks.

Answer 1

How about this solution?
http://superuser.com/questions/49614/excel-get-distinct-values-in-column

Answer 2

You can use SQL with Excel.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

''This is not necessarily the best way to get the workbook name
''that you need
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Note that HDR=Yes, so we can use Group, otherwise columns (fields)
''are named F1, F2 etc
''Pick one:
strSQL = "SELECT DISTINCT Group FROM DataTable" ''Named range
strSQL = "SELECT DISTINCT Group FROM [Sheet1$C3:C67]" ''Range

rs.Open strSQL, cn

Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs