Dynamic "Unique" Records (no blanks)

[Ken]

Excel2003 ... I am always crunching data where I have repeating records & I
often use Advance Filter to extract the "Unique" records ... Then I set
formulas in adjacent Cols that will reach back into the data & return the
various values I need crunched.

Above said ... it would be very helpful to have a dynamic formula ...
perhaps something like ... =unique(range, criteria) ... that would simply
change the "Unique" records list when the data in the range is updated.

On 08/09/07 I posted to this board ... "Return Unique Records ... No Blanks"
.... & received a post back from Ragdyer with a formula that works very well
.... Do I understand how it works? Absolutely not, but it works.

Above said ... I think this capability would be so useful that a simplified
formula would be written to cover it ... I know I am always looking to crunch
numbers off of "Unique" records ... and to have the "Unique" records change
as the body of data changes would be awesome ... (Advance Filter does not do
this)

My "Thanks" to the folks that are intimate with Excel & support these boards
with all of your guidance ... & again to Ragdyer for another nice bail-out
.... Kha

 

[T. Valko]

I also think there should be a built-in function (or 2) for extracting
uniques or extracting uniques with a condition. This is a frequently asked
question in these groups. The advanced filter is simple enough to use but
like you point out, it's not dynamic. There are many formula variations to
do this but most, if not all, of those formulas can be described as rather
complex and not easily understood, if at all, by the average user.

Even more useful would be a function to count uniques with conditions.

 

[Chip Pearson]

Ken,

A distinction needs to be made between the words "unique" and "distinct".
"Unique" values in a range would be those that occur only once in the range,
while "distinct" values would be the first (or other) occurrence of a value
in a range. For example, if you have

A
B
C
<blank>
A
B
<blank>
D

the set of "unique" values would have two elements, "C" and "D", while the
set of "distinct" values would have 4 elements, "A", "B", "C" and "D".

You can use the following array formula in B11:B100 to display the word
"unique" next to the unique elements in A11:A100. It will return an empty
string if the value is A11:A100 is not unique, if the cell in A1:A100 is
empty, or if row containing the formula is greater than the last non-blank
cell in A11:A1000. Change the reference A11:A100 to your range of data, down
to a row past where you would possibly have actual values.

=IF(A11="","",IF(ROW()<=ROW(OFFSET($A$11,MAX(($A$11:$A$100<>"")*(ROW($A$11:$A$100)))-ROW($A$11),0,1,1)),IF(COUNTIF($A$11:$A$100,A11)=1,"unique",""),""))

You can use a very similar formula to display the word "distinct" next to
all the distinct values next to all the distinct values in A11:A100. If a
value occurs more than once, the first occurrence is marked "distinct" and
all subsequent occurrence get a blank string. Blank string will also be
returned if the cell in A11:A100 is blank or if the formula is in a row past
the last non-blank cell in A11:A100.

=IF(A11="","",IF(ROW()<=ROW(OFFSET($A$11,MAX(($A$11:$A$100<>"")*(ROW($A$11:$A$100)))-ROW($A$11),0,1,1)),IF(COUNTIF($A$11:$A11,A11)=1,"distinct",""),""))

Since these are both array formulas, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula in the
formula bar enclosed in curly braces { }.

See www.cpearson.com/Excel/ArrayFormulas.aspx for more details on array
formulas.
See also www.cpearson.com/Excel/Duplicates.aspx and
www.cpearson.com/Excel/ListFunctions.aspx for addition formulas similar to
these.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Ken]

Good morning Chip ...

Ok ... I see your logic (unique vs distinct) ... However, to do this with
Advanced Filter ... Advance Filter indicates it is extracting "Unique" values
from a range based on criteria provided ... And there lies the confusion (at
least for me) ... From your response I would gather that Advance Filter is
really returning "distinct" values from the Range as oppsoed to "unique"
values??? Am I confused? Yes ...

Bottom line ... I would like a Formula to do exactly what Advance Filter
does ... I would like capability to place this formula anywhere on my spread
sheet ... Set the Range & Criteria Range ... Copy the formula down ... Have
Excel return "whatever is being returned when you use Advanced Filter" ... If
data in Range changes ... then values in Col where this formula resides will
also change ...

Thank you too Chip for your many helpful solutions ... I have visited your
web-site a few times ... Kha