Hi:

Is there a function to group items
from (for example) column A to another
part of a workbook or sheet. Im trying
to achieve something like the following.

A C
1 apples apples
2 oranges oranges
3 grapes grapes
4 apples
5 oranges
6 grapes
7 apples
8 oranges
9 grapes

Thanks

TK

You can accomplish this by using Excel's filtering tools. First, select a
cell in the list to be filtered and then turn on autofilter
(Data/FilterAutofilter). Next, select Advanced Filter (Data/Filter/Advanced
Filter). In the dialog box, check the Unique Records Only. If you want to
produce a filtered list separate from your original list, click the option
labeled, "Copy to another location".

HTH
Phil Webb



"TK" wrote in message
...
Hi:

Is there a function to group items
from (for example) column A to another
part of a workbook or sheet. I'm trying
to achieve something like the following.

A C
1 apples apples
2 oranges oranges
3 grapes grapes
4 apples
5 oranges
6 grapes
7 apples
8 oranges
9 grapes

Thanks

TK

TK

See Debra Dalgleish's site for filtering and extracting unique items from a
list.

http://www.contextures.on.ca/xladvfi....html#FilterUR

Gord Dibben Excel MVP

On Tue, 31 Aug 2004 17:27:02 -0700, "TK" wrote:

Hi:

Is there a function to group items
from (for example) column A to another
part of a workbook or sheet. I’m trying
to achieve something like the following.

A C
1 apples apples
2 oranges oranges
3 grapes grapes
4 apples
5 oranges
6 grapes
7 apples
8 oranges
9 grapes

Thanks

TK

Thanks Phil, Gord

Phil's suggestion on advance filters work but exposed the problem that
my actual list has blanks. On Gord's website suggestion I found an
example that dealt with blanks in the list. How lucky can you get with
one post.

Thanks again:



"Gord Dibben" wrote:

TK

See Debra Dalgleish's site for filtering and extracting unique items from a
list.

http://www.contextures.on.ca/xladvfi....html#FilterUR

Gord Dibben Excel MVP

On Tue, 31 Aug 2004 17:27:02 -0700, "TK" wrote:

Hi:

Is there a function to group items
from (for example) column A to another
part of a workbook or sheet. Im trying
to achieve something like the following.

A C
1 apples apples
2 oranges oranges
3 grapes grapes
4 apples
5 oranges
6 grapes
7 apples
8 oranges
9 grapes

Thanks

TK

from Frederic SIGONNEAU (MVP Excel):

(de FS si je ne me trompe)

Sub test() 'appelle ListeValUniques
last = Range("B65000").End(xlUp).Row' data are from A2 to Bxx
ListeValUniques Range("A2:B" & last), Range("C1")
End Sub

Sub ListeValUniques(PlageSrc As Range, CellDest As Range)
'Extrait les valeurs uniques d'une colonne et les renvoie
'dans une autre, à partir de CellDest
Dim Arr1, Elt, Arr2(), Coll As New Collection, I As Integer

'If PlageSrc.Columns.Count 1 Then Exit Sub ' possible sur 2 colonnes
Arr1 = PlageSrc.Value

For Each Elt In Arr1
On Error Resume Next
Coll.Add Elt, CStr(Elt)
If Err.Number = 0 Then
ReDim Preserve Arr2(1 To Coll.Count)
Arr2(Coll.Count) = Elt
End If
On Error GoTo 0
Next
For I = 1 To Coll.Count
Cells(I, 3).Value = Coll.Item(I)
Next
End Sub


--
....Patrick
Quoi que vous fassiez, faites le bien .
Mail: http://cerbermail.com/?KPW0tTCjFw
Connectez vous sur ce forum par :
news://msnews.microsoft.com/microsoft.public.fr.excel


"TK" a écrit dans le message de
...
Hi:

Is there a function to group items
from (for example) column A to another
part of a workbook or sheet. I'm trying
to achieve something like the following.

A C
1 apples apples
2 oranges oranges
3 grapes grapes
4 apples
5 oranges
6 grapes
7 apples
8 oranges
9 grapes

Thanks

TK