Overwriting Duplicates in certain cases

[marshall]

I wonderif you can help.

In my worksheet, I have an alphabetical list of racehorses in column A. in
alphabetical order. In columns B C D ect I have race entries for certain
horses in my own "key" e.g. % could equal The Derby and all horses entered in
the Derby would have a % next to their name (Column B being the Derby etc ec)

Whenever ther are new entries declared in the paper for certain races, I
copy and paste the horses names at the bottom of the worsheet in column A and
in column C would copy paste "£" my "key" for this particular race, say, the
Oaks.

I then click on sort. All the horses get sorted including the new enties.
If there is a duplicate horse however the new entry is shown in addition to
the previous entry for that horse and I have to go thrugh the list deleting
the new entry and putting on the same row as the original entry.
How can I copy, paste and sort so that duplicates keep the original entries
and just update the latest entry.


In columns c d e etc I have a Race. eg. cc or bb whatever "key" I use for
that particular race.

 

[Max]

Perhaps this play using non-array formulas
may provide some ideas ..

A sample construct is available at:
http://www.savefile.com/files/8397248
Extract uniques list in sorted alpha order_marshall_newusers.xls

In sheet: X,

Assume the source table is in A1:C7,
where the existing list of horses is in A2:A4,
with new entrants pasted below in A5 down, eg:

Horses The Derby The Oaks
GaMMA GaMMA% GaMMA£
BETA BETA% BETA£
ALPHA ALPHA% ALPHA£
ZETA ZETA% ZETA£
BETA BETA% BETA£
ALPHA ALPHA% ALPHA£

In a new sheet: Y,

The race will be selected in A1 from a DV droplist

Select A1
Click Data > Validation,
Allow: List
Source: The Derby,The Oaks
(select in A1: The Derby, say)

Put in A2:
=IF(ISERROR(SMALL(B:B,ROW(A1))),"",
INDEX(OFFSET(X!$A:$A,,MATCH($A$1,X!$1:$1,0)-1),
MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Put in B2:
=IF(X!A2="","",
IF(COUNTIF(X!$A$2:A2,X!A2)>1,"",
CODE(UPPER(LEFT(X!A2,1)))+ROW()/10^10))

Select A2:B2, fill down to say, B10, to cover
the max expected extent of data in X's col A.

A2:A10 will return the list of unique horses
in alpha order for the race selected in A1,
all neatly bunched at the top, eg.:

The Derby
ALPHA%
BETA%
GaMMA%
ZETA%

Adapt to suit ..