entering data to overwrite and delete duplicates

[marshall]

In my worksheet I have a list of Racehorses sorted in alphabetical order
Column A

Acambo (GER) A
Accordello a
According To John a
Acropolis (IRE) C
Admiral (IRE) B
Afsoun (FR) D
AI Elle (IRE) B E
Air Force One (GER) D
Akarem A
Akllak (IRE) b E
Albert Mooney (IRE) A C


The Letters are in sucessive columns and refer to forthcoming races ( e.g.
“C†is the Derby ). Each week I update with a new race by scanning from the
racing paper, and then copy paste in to the bottom of the worksheet. I then
enter the next letter for the race in question in the next available column
and then sort. This inevitably produces duplicate horses with the new entry.
How do I delete the duplicate horse but keep the new entry in the same row
as the other entries for that particular horse???? At the moment I am having
to go through the list deleting the duplicates and at the same time moving
the entry race in to the original row.

I look forward to hearing from you

Best

Brian

 

[Max]

I've given a response to your earlier post in .newusers:
http://tinyurl.com/oba5e
(with a sample file to play with)
Not sure whether you might have missed that ..

 

[marshall]

Thank you max. However I don't want a seperatel ist of horses for each race
I just want to add the next race to the exsisting list so that I can scroll
down and see which horses are entered in which races and then print off the
master list.

Can I do that with your formula?

Thank you for your time

 

[Max]

Try this revised sample construct (see sheet: Z)
http://cjoint.com/?cwm4iib2Zz
Extract uniques list in sorted alpha order_v2.xls

In the new sheet: Z,
With A1:B1 housing the name of the races
Slightly revised formula in A2 (to extract for all races)

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

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

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

Sheet Z returns the complete spread for all races

Note that the alpha-sort order is an approx one,
done only using the 1st letter, so it won't be perfect <g>