Hi,
I know how to use the advance filter to extract the unique category, but I
want to know how to calculate subtotal for each unique group and transfer all
these info to another worksheet, in code?

database:
apple,nz
apple,usa
orange,aus
apple,india
grape,aus

newsheet:
apple,3
orange,1
grape,1

Anyone?

I'd record a macro when I did Data|pivottable.

You can count each occurence of the group name.

Add a single row of headers
Select the whole range
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the GroupName button to the row field
drag the GroupName button to the data field
If you don't see "count of", double click on it and choose Count.um of

And finish up the wizard.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

choo wrote:

Hi,
I know how to use the advance filter to extract the unique category, but I
want to know how to calculate subtotal for each unique group and transfer all
these info to another worksheet, in code?

database:
apple,nz
apple,usa
orange,aus
apple,india
grape,aus

newsheet:
apple,3
orange,1
grape,1

Anyone?

--

Dave Peterson

Or you could use the data|filter|advanced filter to extract the unique entries.

Then fill up the adjacent cells with formulas like:

=countif(sheet1!a:a,a2)
(Headers still in row 1)

recording a macro will get you very close.

choo wrote:

Hi,
I know how to use the advance filter to extract the unique category, but I
want to know how to calculate subtotal for each unique group and transfer all
these info to another worksheet, in code?

database:
apple,nz
apple,usa
orange,aus
apple,india
grape,aus

newsheet:
apple,3
orange,1
grape,1

Anyone?

--

Dave Peterson

Thanks Dave.

"Dave Peterson" wrote:

Or you could use the data|filter|advanced filter to extract the unique entries.

Then fill up the adjacent cells with formulas like:

=countif(sheet1!a:a,a2)
(Headers still in row 1)

recording a macro will get you very close.

choo wrote:

Hi,
I know how to use the advance filter to extract the unique category, but I
want to know how to calculate subtotal for each unique group and transfer all
these info to another worksheet, in code?

database:
apple,nz
apple,usa
orange,aus
apple,india
grape,aus

newsheet:
apple,3
orange,1
grape,1

Anyone?

--

Dave Peterson