Prev: Not sure what type of formula to use
Next: if statements for multiple entries
From: Keithlearn on 16 Feb 2010 19:23
I am trying to make a report a little more user friendly for a novice excel
user, and I am trying to use all formula's instead of pivotables on large
data sources. Is there a formula that will pull unique records out of a list
of customer #'s as an example without having to use auto filter which would
then require more manual intervention. I am then using sumproducts using the
customer number to create the report.
From: T. Valko on 16 Feb 2010 22:55
>I am trying to use all formula's instead of pivotables
>on large data sources.

How large is large? More than a few hundred rows?

Formulas to extract unique records are calculation intensive and would slow
things down on "large" amounts of data.

You could probably use a bunch of helper columns that might help speed
things up but then you end up with a bunch of helper columns!

Also, you need to be a bit more specific. Do you want to extract the unique
records from a single column/row or do you want to extract multiple
column/rows of records based on the unique items in a single column/row?

--
Biff
Microsoft Excel MVP


"Keithlearn" <Keithlearn(a)discussions.microsoft.com> wrote in message
news:B54EF3A4-BA6C-48BE-BFDE-3E1467B72089(a)microsoft.com...
>I am trying to make a report a little more user friendly for a novice excel
> user, and I am trying to use all formula's instead of pivotables on large
> data sources. Is there a formula that will pull unique records out of a
> list
> of customer #'s as an example without having to use auto filter which
> would
> then require more manual intervention. I am then using sumproducts using
> the
> customer number to create the report.


From: Max on 17 Feb 2010 09:47
Imo, nothing beats a pivot in ease & speed when it comes to quickly
extracting a list of uniques from a large table of data. In Layout, place the
specific col header into both ROW and DATA (set to Count), and that's it. The
ROW area will return the required uniques list in a flash.
--
Max
Singapore
---
"Keithlearn" wrote:
> I am trying to make a report a little more user friendly for a novice excel
> user, and I am trying to use all formula's instead of pivotables on large
> data sources. Is there a formula that will pull unique records out of a list
> of customer #'s as an example without having to use auto filter which would
> then require more manual intervention. I am then using sumproducts using the
> customer number to create the report.
 | 
Pages: 1
Prev: Not sure what type of formula to use
Next: if statements for multiple entries