Copy Unique Records ... Formula?

I know I can use "Advanced Filter" to copy Unique Records
to another location of spread sheet. Question is ... can
this be achieved with a formula?

Something like this in target cell ...
UniqueRecs(Range,Criteria)

I have several Excel 2000 templates where detailed data is
pasted from Access into the spread sheet ... I format this
data ... then to right of detail I wish to create various
summarizations. (ie: I have 10,000 operations, but only
150 PNs ... the 10,000 records are pasted into
Excel ... "Auto-Filter" Unique records gives the 150 PNs
which I can now write various summarization formulas
against ... However, what I would prefer to do ... Is to
go to the target Cell & simply have a formula there that
would automatically fill down the Unique records found
when the initial past of the detail occurs.

Can this be achieved??? ... Thanks ... Kha

Check out:
http://www.contextures.com/excelfiles.html
Under Functions (near bottom of page), then number 2
Extract List of Unique Items -- Use formulas to extract unique items from a
list. Based on an example from Paul Cumbers.
This might help you!


"Ken" wrote in message
...
I know I can use "Advanced Filter" to copy Unique Records
to another location of spread sheet. Question is ... can
this be achieved with a formula?

Something like this in target cell ...
UniqueRecs(Range,Criteria)

I have several Excel 2000 templates where detailed data is
pasted from Access into the spread sheet ... I format this
data ... then to right of detail I wish to create various
summarizations. (ie: I have 10,000 operations, but only
150 PNs ... the 10,000 records are pasted into
Excel ... "Auto-Filter" Unique records gives the 150 PNs
which I can now write various summarization formulas
against ... However, what I would prefer to do ... Is to
go to the target Cell & simply have a formula there that
would automatically fill down the Unique records found
when the initial past of the detail occurs.

Can this be achieved??? ... Thanks ... Kha

See revised Method 1 described at

http://makeashorterlink.com/?P55021C06

and http://makeashorterlink.com/?T22A12286

which takes up the method JMay refers to.

"Ken" wrote in message
...
I know I can use "Advanced Filter" to copy Unique Records
to another location of spread sheet. Question is ... can
this be achieved with a formula?

Something like this in target cell ...
UniqueRecs(Range,Criteria)

I have several Excel 2000 templates where detailed data is
pasted from Access into the spread sheet ... I format this
data ... then to right of detail I wish to create various
summarizations. (ie: I have 10,000 operations, but only
150 PNs ... the 10,000 records are pasted into
Excel ... "Auto-Filter" Unique records gives the 150 PNs
which I can now write various summarization formulas
against ... However, what I would prefer to do ... Is to
go to the target Cell & simply have a formula there that
would automatically fill down the Unique records found
when the initial past of the detail occurs.

Can this be achieved??? ... Thanks ... Kha

"Aladin Akyurek" wrote...
See revised Method 1 described at

http://makeashorterlink.com/?P55021C06

and http://makeashorterlink.com/?T22A12286

which takes up the method JMay refers to.

Depending on what the OP's criteria may be, these may be either too much or too
little. Without any criteria other than no duplicates, there's no need for
ancillary calculations. If there were N distinct entries in List, then only N
formulas are required to extract the N distinct entries.

Top cell (N2):
=INDEX(List,1)

Next cell (N3): [array formula]
=INDEX(List,MATCH(0,COUNTIF(N$2:N2,List),0))

Fill N3 down until the formula evaluates as #N/A. If there are criteria
involved, e.g., pulling only entries from List corresponding to values over 50
in Amount, change the formulas to

Top cell (N2): [array formula]
=INDEX(List,MATCH(TRUE,Amounts50,0))

Next cell (N3): [array formula]
=INDEX(List,MATCH(0,COUNTIF(N$2:N2,List)+1-(Amounts50),0))

That said, wrapping Data Filter Advanced Filter, Copy, Unique inside a macro
would provide a more efficient runtime solution, especially if the selection
criteria were complex.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

"Harlan Grove" wrote in message
...
"Aladin Akyurek" wrote...
See revised Method 1 described at

http://makeashorterlink.com/?P55021C06

and http://makeashorterlink.com/?T22A12286

which takes up the method JMay refers to.

Depending on what the OP's criteria may be, these may be either too much
or too
little. Without any criteria other than no duplicates, there's no need for
ancillary calculations. [...]

Method 1 sorts, eliminates duplicates and blanks from a 1D range. It's quite
fast. The link I quoted lists lots of methods. We should also add the
formula set you posted to the collection, along with the Paul Cumbers
method, enhanced for robustness as I suggested. Next step would be some
performance tests.

"Aladin Akyurek" wrote...
...
Method 1 sorts, eliminates duplicates and blanks from a 1D range. It's quite
fast. The link I quoted lists lots of methods. We should also add the
formula set you posted to the collection, along with the Paul Cumbers
method, enhanced for robustness as I suggested. Next step would be some
performance tests.

To be honest, I gave up trying to find method 1 in the morass of MrExcel. Does
it need only N cells to pull N distinct entries from a list? If not, it may be
faster, but it likely uses more memory, thus a trade-off.

As for time trials, I'd suspect a macro running an advanced filter extracting
'unique' values would be faster than most formula methods if the list involved
were large (10K or more records).

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.