GROUP
 

Hello,

I am looking for a function similar to the SQL function GROUP BY in
excel.

Basically what I want to do is make a list of all the unique variables
in a list. After that I want to use the COUNTIF function to make a
frequency table.

Thus from the list:
a
b
a
c
a
c

I want to get the list

a
b
c

Only the unique variables.

I am surprised that EXCEL doesn't have a pre-defined function for
this.

Anyone knows how to do it?

Jochem

GROUP
 

Data=filter=Advanced filter, select copy to another location and click
the Uniques checkbox in the lower left corner.

This must be done each time you want the list. There is no built in
worksheet function that does this although you might be able to combine
several functions in a formula to achieve that result.

--
Regards,
Tom Ogilvy

"Jochem" wrote in message
om...
Hello,

I am looking for a function similar to the SQL function GROUP BY in
excel.

Basically what I want to do is make a list of all the unique variables
in a list. After that I want to use the COUNTIF function to make a
frequency table.

Thus from the list:
a
b
a
c
a
c

I want to get the list

a
b
c

Only the unique variables.

I am surprised that EXCEL doesn't have a pre-defined function for
this.

Anyone knows how to do it?

Jochem

GROUP
 

You could accomplish this manually using the DataFilterAdvanced
Filter command. Select "Unique Records Only".

GROUP
 

Take advantage of the Collection, which refuses to accept identical key
strings in its members.
Ignore the errors it generates & keep going.
try this...
select your (single) column data which needs to be 'grouped by'
this code will return the result set beginning in row 2 (assuming a header),
two columns away from your selected data

Sub GroupBy()
intResultColumn = Selection.Offset(0,2).Column
On Error Resume Next
Dim col As New Collection
For Each x In Selection
col.Add x.Value, x.Value
Next x
On Error GoTo 0
intCount = 1
For Each y In col
intCount = intCount + 1
ActiveSheet.Cells(intCount, intResultColumn).Value = y
Next y
End Sub



"Jochem" wrote in message
om...
Hello,

I am looking for a function similar to the SQL function GROUP BY in
excel.

Basically what I want to do is make a list of all the unique variables
in a list. After that I want to use the COUNTIF function to make a
frequency table.

Thus from the list:
a
b
a
c
a
c

I want to get the list

a
b
c

Only the unique variables.

I am surprised that EXCEL doesn't have a pre-defined function for
this.

Anyone knows how to do it?

Jochem