Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Overwriting Duplicates in certain cases
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Overwriting Duplicates in certain cases
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "marshall" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicates then calculate..??? | Excel Worksheet Functions | |||
Duplicates | Excel Discussion (Misc queries) | |||
Find and mark duplicates | Excel Discussion (Misc queries) | |||
Finding Duplicates | Excel Worksheet Functions | |||
worksheet change and cases | Excel Worksheet Functions |