Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GROUP
You could accomplish this manually using the DataFilterAdvanced
Filter command. Select "Unique Records Only". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
Taking age group Ie ages 20-29 and picking out net sales for group | Excel Worksheet Functions | |||
How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Get the name of a group given the reference to a shape within the group | Excel Programming |