Given the initial sample data, is there a formula that can take the max of
the column nex to duplicates, with the fact that the number of duplicates
could vary)?

So original data was, and let's assume there is data next to it in the next
column:
AB 2
AB-C 3
DEF 4
DEF 6
DEF-GH 3.4
DEF-GHI 3.8
DEF-GHI 4.5
JKL 7
JKL-M 7
JKL-MN 7.9
JKL-MN 7.9
JKL-MN 8.5

So with a formula the data then takes out the non-duplicates as we discussed
but also gives back the max. Now max part cna be a separat formula because I
can go back to the data to grab it but I just need to know how I can do this
in a contiguous way. Sample answer to that data would be:
AB 2
AB-C 3
DEF 6
DEF-GH 3.4
DEF-GHI 4.5
JKL 7
JKL-M 7
JKL-MN 8.5

"RagDyer" wrote:

Couple of options:

Enter this formula in B1, and copy down as needed:

=IF(COUNTIF(A$1:A1,A1)=1,A1,"")

HOWEVER, this will *not* produce a contiguous list.
There will be blank rows where it finds duplicates in the adjoining Column
A.

To display a contiguous list, with B1 containing
=A1
try this *array* formula in *B2*

=IF(ISERR(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 until you get a blank cell.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"A.S." wrote in message
...
I need a formula that does this. I realize that this can be done by data
filter, however, is the formula that can do this?

"Dave Peterson" wrote:

I'd a a header in row 1 and then use Data|Filter|Advanced filter and use
the
"Unique records only" checkbox.

Debra Dalgleish describes it he
http://contextures.com/xladvfilter01.html#FilterUR

A.S. wrote:

I am trying to do the following. Assume data starting in row A1:

AB
AB-C
DEF
DEF
DEF-GH
DEF-GHI
DEF-GHI
JKL
JKL-M
JKL-MN
JKL-MN
JKL-MN

Basically, what I would like to happen starting in B2 (since B1 will
alwaysequal to A1), is that it looks at B2, and if B2 is not a
duplicate of
anything above it puts B2, otherwise, if it is a duplicate it moves on
and
puts B3, so long as B3 is not a duplicate of anything from above, and
so and
so forth. So the answer to the above data would look like:

AB
AB-C
DEF
DEF-GH
DEF-GHI
JKL
JKL-M
JKL-MN

--

Dave Peterson