Formula to summarize data

[tomhelle]

I have a list of materials in column A. Some of the materials repeat in
various rows. I want to formula to summarize the list into a condensed list
of materials.

Many thanks in advance

 

[Gord Dibben]

Data>Filter>Advanced Filter.

Copy "unique records only" to another place.


Gord Dibben MS Excel MVP

 

[KC Rippstein hotmail com>]

Run a pivot table to get a list of all unique entries from Column A.

 

[tomhelle]

I should have mentioned that I want a formula that would automatically update
the summarized list. I need a solution that would be totally transparent to
the user.

Thanks

 

[RagDyer]

Say you anticipate the range of A1 to A200 will eventually be used for this
materials list, and you're looking to make a unique list in Column B.

In B1 enter:
=A1

In B2 enter this *array* formula:

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK(
$A$1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as far as needed.