Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
entering data to overwrite and delete duplicates
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
entering data to overwrite and delete duplicates
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "marshall" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
entering data to overwrite and delete duplicates
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" wrote: 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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "marshall" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
entering data to overwrite and delete duplicates
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,"",COD E(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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "marshall" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - external data | Excel Discussion (Misc queries) | |||
data range props | Excel Discussion (Misc queries) | |||
data range | Excel Discussion (Misc queries) | |||
Data range properties | Excel Discussion (Misc queries) | |||
Problem with external data | Excel Discussion (Misc queries) |