Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.

Joe,
Almost as difficult to explain an answer for me as for you to describe. So
I'll try by giving an example based on the workbook you linked to. Look at
the last sheet, OVERALL 1965-1967.

Let's say that you want to add new names into column F (Fast Qualifiers
Driver) and that the source list that might have new (or duplicate names) is
the list over in column B (Feature Wins Driver). I realize the list is going
to be on another sheet, but this works the same way and will let you see
what's going on on a single sheet.

Right below the last name in column F, at cell F24, enter this formula:
=IF(COUNTIF(F$6:F23,B6)0,"",B6)
then simply drag/fill that on down the column for as many entries as there
are names in column B. You'll know when you've gone far enough because when
you get far enough down, instead of a name or empty cell showing, a '0'
(zero) will appear in the cell.

What this does is take the name in colum B, row by row, and compare it to
the names already in (or just added to) column F. If the name isn't already
in column F, it shows the name, but if it's already in it, then it shows a
blank.

I realize this leaves ugly gaps in the list and also if you sort that list.
You can deal with that part of the problem in one of several ways:
1 - auto-filter to show only non-blank entries, but that may mess up display
of information in other rows.
2 - manually delete the empty cells, choosing the 'move cells up' option to
keep from deleting entire rows. This is probably almost as time consuming as
working through and deleting duplicate names manually, just a little easier
and more certain of not accidentally either missing a duplicate or deleting a
one-and-only entry.
3 - Select the cells with in the column (F) and use Edit | Copy
then without unselecting the cells, use
Edit | Paste Special and select the [Values] option.
That will remove the formulas, leaving either the names or blanks that were
being displayed. You could then start copying names up from the bottom out
of cells where they are displayed to 'join up' with names above them, and
repeat that process until you have a continuous list of names.

If I can be of any further assistance with this, feel free to contact me at
(remove spaces) HelpFrom @ jlathamsite.com if you feel it would take a long
involved discussion to carry on, otherwise, questions here have an
opportunity to get an answer from me or the many other really sharp folks
hanging around.

By the way, when working with multiple sheets, to get the reference to the
equivalent of B6 in our formula above, you can simply choose the appropriate
sheet and cell and click on it and Excel will put the proper reference into
the formula at that point, then just type a comma and continue with the
formula. It would end up looking something like (just an example)
=IF(COUNTIF(F$6:F23,'SUPER MODS 1965-1967'!B9)0,"",'SUPER MODS 1965-1967'!B9)


"Joe" wrote:

Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.

Joe,

JLatham, one of the top professionals that helps people on this board has
done the best that Excel formulas allow to provide exactly what you asked
for, i.e. a formulaic way to get rid of duplicate data in a column. He
deserves KUDOS and a check mark for the effort, but the function-based
solution that you asked for still potentially leaves you with a considerable
amount of work to do. This is the nature of the beast, Excel formulas and
functions are not designed to do what you were asking for. Latham's solution
is as close as I have seen and to see how dificult it is to get Excel to even
pretend to do what you asked, consider that most questions to this board are
answered within an hour or so, some with 4 or more replies. Latham's response
came over 7 1/2 hours later.

I have had to solve the duplicate data problem a number of times; I gave up
on formulaic solutions long ago. So while Latham gave you the closest
formulaic answer to exactly what you specified in your question, I will share
the method I have worked out in the past.

You need a macro to do what you really need to do. NOTE: I needed to
re-create the macro and while I was researching the exact syntax for a term I
found a better example of the code I was writing. The macro below is a
fairly-well modified version (adjusted to fit this problem) of what I found
in the Microsoft Excel VBA

Help files; to see the original code, search for "Delete Method" in Help
from inside the Visual Basic Editor.

Warning: to start, make a copy of your workbook and work on the copy until
you understand and are well practiced in the use of any macro that delets
data. It may take a couple of practice runs to get the whole picture of what
you are doing. When you do understand, you will wonder how you lived without
VBA.

In your copy workbook, with a compiled data worksheet showing (such as
"OVERALL 1965-1967", you should go to Tools|Macros|Visual Basic Editor
and insert a module in your workbook;
then paste the following code into the module:

********************
Sub RidDups()

Dim r1 As Range 'next row
Dim r2 As Range 'current row
Dim r3 As Range 'three cells to delete because of duplicate driver

With ActiveSheet

Set r2 = Selection.Range("A1")

Do While Not IsEmpty(r2)
Set r1 = r2.Offset(1, 0)
If r1.Value = r2.Value Then
Set r3 = r2.Offset(0, -1)
r3.Resize(1, 3).Select
Set r3 = Selection
r3.Cells.Delete Shift:=xlShiftUp
End If
Set r2 = r1
Loop

End With

End Sub
*******************

Don't bother to paste the stars.

NEXT:
Then go to the data compiling worksheet (in the example you posted this is:
"OVERALL 1965-1967".
Then go to a column of drivers where you have pasted new data. Work with one
race type at a time.
The RidDups macro needs the drivers sorted, in the example workbook they
were not perfectly sorted.
Sort the 3 adjacent columns (rank, driver, wins) according to only the
driver column, decending. To do so, just select (click on) the word 'Rank' at
the top of the chosen list, then go to Data|Sort|"Driver"|Assending.
Excel will select the three columns for you. Note: This may temporarily
discombobulate your formulas, they will be quickly fixed at the end.
After sorting, NEXT:
Select just the drivers in the middle column, all the way down. Click on the
driver's name in the top cell of the list, then press the control/shift/down
keys.
With that selection still darkened, go to Tools|Macros|This Workbook
Only|RidDups|Run.
That will compare each name with the next and delete the duplicates, VERY
quickly.

NOTE: If you get tired of the screen flashing while the macro is running,
modify the code by putting two new

lines in it:
Right after the three 'Dim' statements, type this new line:
Application.ScreenUpdating = False
Then right after the 'End With' statement, type this new line:
Application.ScreenUpdating = True
These lines will make the screen stop flickering and flashing while the code
is running and will speed up excecution. I should wait until you are
comfortable with the operation of the macro first.

After deleting the duplicates using the Macro; NEXT:
Check your list for the few remaining data problems, for instance, there
might or might not be both of each

of these:
BILL DENNIGER
BILL DENNINGER
BILL HEENEY
BILL HENNEY
DON QUACKENBUSH
DON QUAKENBUSH
HURAM LOCKWOOD
HURAN LOCKWOOD
JIM GREASLEY
JIM GRESLEY
JOHN LOGAN
JOHNNY LOGAN
LEON WEISKE
LEON WIESKE

These may all be legitimate, or some may be misspellings/nicknames.
As you can see, the amount of work to fix the list is much less after
running the macro; these are the only potential duplicates left in the list
that I tested it on, as far as i can tell.
Also NOTE: Jim Gresely's name has a space in front it. If it doesn't appear
exactly like that in each winners list on each data-feeding worksheet, then
your 'wins' formula will not be able to find him and he will always show zero
wins.
NEXT:
Fix the formulas in the wins column by finding a good copy of the formula
left over (there will be a bunch of good ones left in the list, just a few
may be messed up or missing) and then drag-copying the good formula up and
down the remaining list.
NEXT:
Data|Sort the entire list again, this time according to Wins|Decending
/Drivers|Acending. This puts the winningest drivers back on top and the tied
drivers in alphabetical order.
NEXT:
Your rank numbers will now be somewhat messed up, no worries, it was already
somewhat messed up to begin with. You may want to give them a true ranking,
taking into account ties. To do this, go to the first driver.

In the example sheet I worked on the Pre-LIM Wins set of data, so I went to
BOB SENNEKER with 38 wins. Select the cell for BOB's rank, cell I6 (EYE SIX,
not Sixteen).
In i6, type the formula:
=IF(K6<K5,COUNTA($J$6:J6),I5)

Read this formula as, {IF This driver's wins are not equal to the previous
driver's wins (both in column K),
THEN Count all drivers to this point (In column J),
ELSE This driver's rank is tied (equal to) the previous driver's rank (In
column eye).}

Note the Dollar-signs in the first term inside the COUNTA function, this
locks all of the formulas in the column to the top cell while the second term
is adjusted by Excel to match where you copy it.
Copy that forumla all the way down the Rank column.
This formula will make the drivers with the same amount of wins tied in
rank, and the next driver with less wins will pick up the correct next number
(in the example worksheet in the PRE-LIMS WINS data table, there are three
drivers with 16 wins who are tied at fourth, the next two drivers have 10
wins and are tied for seventh,
towards the bottom of the list a whole bunch with 2 wins are all tied at
44th, and the 1-win "wild" bunch are tied at 74th.)


Please let us know if any of this helped. Certainly Latham deserves a check
in the was this helpful/did this answer your question check box because he
really did answer your question.

SongBear

"Joe" wrote:

Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.

SongBear,
Thanks for the high praise - you're correct - I gave Joe the best that I
could think of in what Excel offers through worksheet formulas. I also agree
that a VBA solution is the easiest, surest way to do this. Very good of you
to share a known, working code solution with him.

When Joe comes back (not sure he has yet), then he'll have a couple of
choices of how to tackle his problem.

P.S. - Joe, no need for a tray for me. I do it just 'cause it's fun and
every once in a while I actually manage to help someone.

"SongBear" wrote:

Joe,

JLatham, one of the top professionals that helps people on this board has
done the best that Excel formulas allow to provide exactly what you asked
for, i.e. a formulaic way to get rid of duplicate data in a column. He
deserves KUDOS and a check mark for the effort, but the function-based
solution that you asked for still potentially leaves you with a considerable
amount of work to do. This is the nature of the beast, Excel formulas and
functions are not designed to do what you were asking for. Latham's solution
is as close as I have seen and to see how dificult it is to get Excel to even
pretend to do what you asked, consider that most questions to this board are
answered within an hour or so, some with 4 or more replies. Latham's response
came over 7 1/2 hours later.

I have had to solve the duplicate data problem a number of times; I gave up
on formulaic solutions long ago. So while Latham gave you the closest
formulaic answer to exactly what you specified in your question, I will share
the method I have worked out in the past.

You need a macro to do what you really need to do. NOTE: I needed to
re-create the macro and while I was researching the exact syntax for a term I
found a better example of the code I was writing. The macro below is a
fairly-well modified version (adjusted to fit this problem) of what I found
in the Microsoft Excel VBA

Help files; to see the original code, search for "Delete Method" in Help
from inside the Visual Basic Editor.

Warning: to start, make a copy of your workbook and work on the copy until
you understand and are well practiced in the use of any macro that delets
data. It may take a couple of practice runs to get the whole picture of what
you are doing. When you do understand, you will wonder how you lived without
VBA.

In your copy workbook, with a compiled data worksheet showing (such as
"OVERALL 1965-1967", you should go to Tools|Macros|Visual Basic Editor
and insert a module in your workbook;
then paste the following code into the module:

********************
Sub RidDups()

Dim r1 As Range 'next row
Dim r2 As Range 'current row
Dim r3 As Range 'three cells to delete because of duplicate driver

With ActiveSheet

Set r2 = Selection.Range("A1")

Do While Not IsEmpty(r2)
Set r1 = r2.Offset(1, 0)
If r1.Value = r2.Value Then
Set r3 = r2.Offset(0, -1)
r3.Resize(1, 3).Select
Set r3 = Selection
r3.Cells.Delete Shift:=xlShiftUp
End If
Set r2 = r1
Loop

End With

End Sub
*******************

Don't bother to paste the stars.

NEXT:
Then go to the data compiling worksheet (in the example you posted this is:
"OVERALL 1965-1967".
Then go to a column of drivers where you have pasted new data. Work with one
race type at a time.
The RidDups macro needs the drivers sorted, in the example workbook they
were not perfectly sorted.
Sort the 3 adjacent columns (rank, driver, wins) according to only the
driver column, decending. To do so, just select (click on) the word 'Rank' at
the top of the chosen list, then go to Data|Sort|"Driver"|Assending.
Excel will select the three columns for you. Note: This may temporarily
discombobulate your formulas, they will be quickly fixed at the end.
After sorting, NEXT:
Select just the drivers in the middle column, all the way down. Click on the
driver's name in the top cell of the list, then press the control/shift/down
keys.
With that selection still darkened, go to Tools|Macros|This Workbook
Only|RidDups|Run.
That will compare each name with the next and delete the duplicates, VERY
quickly.

NOTE: If you get tired of the screen flashing while the macro is running,
modify the code by putting two new

lines in it:
Right after the three 'Dim' statements, type this new line:
Application.ScreenUpdating = False
Then right after the 'End With' statement, type this new line:
Application.ScreenUpdating = True
These lines will make the screen stop flickering and flashing while the code
is running and will speed up excecution. I should wait until you are
comfortable with the operation of the macro first.

After deleting the duplicates using the Macro; NEXT:
Check your list for the few remaining data problems, for instance, there
might or might not be both of each

of these:
BILL DENNIGER
BILL DENNINGER
BILL HEENEY
BILL HENNEY
DON QUACKENBUSH
DON QUAKENBUSH
HURAM LOCKWOOD
HURAN LOCKWOOD
JIM GREASLEY
JIM GRESLEY
JOHN LOGAN
JOHNNY LOGAN
LEON WEISKE
LEON WIESKE

These may all be legitimate, or some may be misspellings/nicknames.
As you can see, the amount of work to fix the list is much less after
running the macro; these are the only potential duplicates left in the list
that I tested it on, as far as i can tell.
Also NOTE: Jim Gresely's name has a space in front it. If it doesn't appear
exactly like that in each winners list on each data-feeding worksheet, then
your 'wins' formula will not be able to find him and he will always show zero
wins.
NEXT:
Fix the formulas in the wins column by finding a good copy of the formula
left over (there will be a bunch of good ones left in the list, just a few
may be messed up or missing) and then drag-copying the good formula up and
down the remaining list.
NEXT:
Data|Sort the entire list again, this time according to Wins|Decending
/Drivers|Acending. This puts the winningest drivers back on top and the tied
drivers in alphabetical order.
NEXT:
Your rank numbers will now be somewhat messed up, no worries, it was already
somewhat messed up to begin with. You may want to give them a true ranking,
taking into account ties. To do this, go to the first driver.

In the example sheet I worked on the Pre-LIM Wins set of data, so I went to
BOB SENNEKER with 38 wins. Select the cell for BOB's rank, cell I6 (EYE SIX,
not Sixteen).
In i6, type the formula:
=IF(K6<K5,COUNTA($J$6:J6),I5)

Read this formula as, {IF This driver's wins are not equal to the previous
driver's wins (both in column K),
THEN Count all drivers to this point (In column J),
ELSE This driver's rank is tied (equal to) the previous driver's rank (In
column eye).}

Note the Dollar-signs in the first term inside the COUNTA function, this
locks all of the formulas in the column to the top cell while the second term
is adjusted by Excel to match where you copy it.
Copy that forumla all the way down the Rank column.
This formula will make the drivers with the same amount of wins tied in
rank, and the next driver with less wins will pick up the correct next number
(in the example worksheet in the PRE-LIMS WINS data table, there are three
drivers with 16 wins who are tied at fourth, the next two drivers have 10
wins and are tied for seventh,
towards the bottom of the list a whole bunch with 2 wins are all tied at
44th, and the 1-win "wild" bunch are tied at 74th.)


Please let us know if any of this helped. Certainly Latham deserves a check
in the was this helpful/did this answer your question check box because he
really did answer your question.

SongBear

"Joe" wrote:

Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.

ouch.....my head is swimming :))
I have printed them off and shall play with them both soon and see if I can
make them work without getting too frustrated. I noticed the other day when
looking at one of the lists, that the next open cell has a drop down list,
alphabetically of all names already in that list. so i used that the other
day so I didn't duplicate names for that year. But I too shall the other
methods you both suggested and appreciate your courtesy. My new concern would
be the size of the workbook when done. My offer still stands, if you send me
an email through our work site for the keyboard trays.

"JLatham" wrote:

SongBear,
Thanks for the high praise - you're correct - I gave Joe the best that I
could think of in what Excel offers through worksheet formulas. I also agree
that a VBA solution is the easiest, surest way to do this. Very good of you
to share a known, working code solution with him.

When Joe comes back (not sure he has yet), then he'll have a couple of
choices of how to tackle his problem.

P.S. - Joe, no need for a tray for me. I do it just 'cause it's fun and
every once in a while I actually manage to help someone.

"SongBear" wrote:

Joe,

JLatham, one of the top professionals that helps people on this board has
done the best that Excel formulas allow to provide exactly what you asked
for, i.e. a formulaic way to get rid of duplicate data in a column. He
deserves KUDOS and a check mark for the effort, but the function-based
solution that you asked for still potentially leaves you with a considerable
amount of work to do. This is the nature of the beast, Excel formulas and
functions are not designed to do what you were asking for. Latham's solution
is as close as I have seen and to see how dificult it is to get Excel to even
pretend to do what you asked, consider that most questions to this board are
answered within an hour or so, some with 4 or more replies. Latham's response
came over 7 1/2 hours later.

I have had to solve the duplicate data problem a number of times; I gave up
on formulaic solutions long ago. So while Latham gave you the closest
formulaic answer to exactly what you specified in your question, I will share
the method I have worked out in the past.

You need a macro to do what you really need to do. NOTE: I needed to
re-create the macro and while I was researching the exact syntax for a term I
found a better example of the code I was writing. The macro below is a
fairly-well modified version (adjusted to fit this problem) of what I found
in the Microsoft Excel VBA

Help files; to see the original code, search for "Delete Method" in Help
from inside the Visual Basic Editor.

Warning: to start, make a copy of your workbook and work on the copy until
you understand and are well practiced in the use of any macro that delets
data. It may take a couple of practice runs to get the whole picture of what
you are doing. When you do understand, you will wonder how you lived without
VBA.

In your copy workbook, with a compiled data worksheet showing (such as
"OVERALL 1965-1967", you should go to Tools|Macros|Visual Basic Editor
and insert a module in your workbook;
then paste the following code into the module:

********************
Sub RidDups()

Dim r1 As Range 'next row
Dim r2 As Range 'current row
Dim r3 As Range 'three cells to delete because of duplicate driver

With ActiveSheet

Set r2 = Selection.Range("A1")

Do While Not IsEmpty(r2)
Set r1 = r2.Offset(1, 0)
If r1.Value = r2.Value Then
Set r3 = r2.Offset(0, -1)
r3.Resize(1, 3).Select
Set r3 = Selection
r3.Cells.Delete Shift:=xlShiftUp
End If
Set r2 = r1
Loop

End With

End Sub
*******************

Don't bother to paste the stars.

NEXT:
Then go to the data compiling worksheet (in the example you posted this is:
"OVERALL 1965-1967".
Then go to a column of drivers where you have pasted new data. Work with one
race type at a time.
The RidDups macro needs the drivers sorted, in the example workbook they
were not perfectly sorted.
Sort the 3 adjacent columns (rank, driver, wins) according to only the
driver column, decending. To do so, just select (click on) the word 'Rank' at
the top of the chosen list, then go to Data|Sort|"Driver"|Assending.
Excel will select the three columns for you. Note: This may temporarily
discombobulate your formulas, they will be quickly fixed at the end.
After sorting, NEXT:
Select just the drivers in the middle column, all the way down. Click on the
driver's name in the top cell of the list, then press the control/shift/down
keys.
With that selection still darkened, go to Tools|Macros|This Workbook
Only|RidDups|Run.
That will compare each name with the next and delete the duplicates, VERY
quickly.

NOTE: If you get tired of the screen flashing while the macro is running,
modify the code by putting two new

lines in it:
Right after the three 'Dim' statements, type this new line:
Application.ScreenUpdating = False
Then right after the 'End With' statement, type this new line:
Application.ScreenUpdating = True
These lines will make the screen stop flickering and flashing while the code
is running and will speed up excecution. I should wait until you are
comfortable with the operation of the macro first.

After deleting the duplicates using the Macro; NEXT:
Check your list for the few remaining data problems, for instance, there
might or might not be both of each

of these:
BILL DENNIGER
BILL DENNINGER
BILL HEENEY
BILL HENNEY
DON QUACKENBUSH
DON QUAKENBUSH
HURAM LOCKWOOD
HURAN LOCKWOOD
JIM GREASLEY
JIM GRESLEY
JOHN LOGAN
JOHNNY LOGAN
LEON WEISKE
LEON WIESKE

These may all be legitimate, or some may be misspellings/nicknames.
As you can see, the amount of work to fix the list is much less after
running the macro; these are the only potential duplicates left in the list
that I tested it on, as far as i can tell.
Also NOTE: Jim Gresely's name has a space in front it. If it doesn't appear
exactly like that in each winners list on each data-feeding worksheet, then
your 'wins' formula will not be able to find him and he will always show zero
wins.
NEXT:
Fix the formulas in the wins column by finding a good copy of the formula
left over (there will be a bunch of good ones left in the list, just a few
may be messed up or missing) and then drag-copying the good formula up and
down the remaining list.
NEXT:
Data|Sort the entire list again, this time according to Wins|Decending
/Drivers|Acending. This puts the winningest drivers back on top and the tied
drivers in alphabetical order.
NEXT:
Your rank numbers will now be somewhat messed up, no worries, it was already
somewhat messed up to begin with. You may want to give them a true ranking,
taking into account ties. To do this, go to the first driver.

In the example sheet I worked on the Pre-LIM Wins set of data, so I went to
BOB SENNEKER with 38 wins. Select the cell for BOB's rank, cell I6 (EYE SIX,
not Sixteen).
In i6, type the formula:
=IF(K6<K5,COUNTA($J$6:J6),I5)

Read this formula as, {IF This driver's wins are not equal to the previous
driver's wins (both in column K),
THEN Count all drivers to this point (In column J),
ELSE This driver's rank is tied (equal to) the previous driver's rank (In
column eye).}

Note the Dollar-signs in the first term inside the COUNTA function, this
locks all of the formulas in the column to the top cell while the second term
is adjusted by Excel to match where you copy it.
Copy that forumla all the way down the Rank column.
This formula will make the drivers with the same amount of wins tied in
rank, and the next driver with less wins will pick up the correct next number
(in the example worksheet in the PRE-LIMS WINS data table, there are three
drivers with 16 wins who are tied at fourth, the next two drivers have 10
wins and are tied for seventh,
towards the bottom of the list a whole bunch with 2 wins are all tied at
44th, and the 1-win "wild" bunch are tied at 74th.)


Please let us know if any of this helped. Certainly Latham deserves a check
in the was this helpful/did this answer your question check box because he
really did answer your question.

SongBear

"Joe" wrote:

Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.

Don't worry about the tray - just be sure and holler if you're having
troubles with all of this. There is always at least an attempt to help from
HelpFrom @ jlathamsite.com (remove spaces)


"Joe" wrote:

ouch.....my head is swimming :))
I have printed them off and shall play with them both soon and see if I can
make them work without getting too frustrated. I noticed the other day when
looking at one of the lists, that the next open cell has a drop down list,
alphabetically of all names already in that list. so i used that the other
day so I didn't duplicate names for that year. But I too shall the other
methods you both suggested and appreciate your courtesy. My new concern would
be the size of the workbook when done. My offer still stands, if you send me
an email through our work site for the keyboard trays.

"JLatham" wrote:

SongBear,
Thanks for the high praise - you're correct - I gave Joe the best that I
could think of in what Excel offers through worksheet formulas. I also agree
that a VBA solution is the easiest, surest way to do this. Very good of you
to share a known, working code solution with him.

When Joe comes back (not sure he has yet), then he'll have a couple of
choices of how to tackle his problem.

P.S. - Joe, no need for a tray for me. I do it just 'cause it's fun and
every once in a while I actually manage to help someone.

"SongBear" wrote:

Joe,

JLatham, one of the top professionals that helps people on this board has
done the best that Excel formulas allow to provide exactly what you asked
for, i.e. a formulaic way to get rid of duplicate data in a column. He
deserves KUDOS and a check mark for the effort, but the function-based
solution that you asked for still potentially leaves you with a considerable
amount of work to do. This is the nature of the beast, Excel formulas and
functions are not designed to do what you were asking for. Latham's solution
is as close as I have seen and to see how dificult it is to get Excel to even
pretend to do what you asked, consider that most questions to this board are
answered within an hour or so, some with 4 or more replies. Latham's response
came over 7 1/2 hours later.

I have had to solve the duplicate data problem a number of times; I gave up
on formulaic solutions long ago. So while Latham gave you the closest
formulaic answer to exactly what you specified in your question, I will share
the method I have worked out in the past.

You need a macro to do what you really need to do. NOTE: I needed to
re-create the macro and while I was researching the exact syntax for a term I
found a better example of the code I was writing. The macro below is a
fairly-well modified version (adjusted to fit this problem) of what I found
in the Microsoft Excel VBA

Help files; to see the original code, search for "Delete Method" in Help
from inside the Visual Basic Editor.

Warning: to start, make a copy of your workbook and work on the copy until
you understand and are well practiced in the use of any macro that delets
data. It may take a couple of practice runs to get the whole picture of what
you are doing. When you do understand, you will wonder how you lived without
VBA.

In your copy workbook, with a compiled data worksheet showing (such as
"OVERALL 1965-1967", you should go to Tools|Macros|Visual Basic Editor
and insert a module in your workbook;
then paste the following code into the module:

********************
Sub RidDups()

Dim r1 As Range 'next row
Dim r2 As Range 'current row
Dim r3 As Range 'three cells to delete because of duplicate driver

With ActiveSheet

Set r2 = Selection.Range("A1")

Do While Not IsEmpty(r2)
Set r1 = r2.Offset(1, 0)
If r1.Value = r2.Value Then
Set r3 = r2.Offset(0, -1)
r3.Resize(1, 3).Select
Set r3 = Selection
r3.Cells.Delete Shift:=xlShiftUp
End If
Set r2 = r1
Loop

End With

End Sub
*******************

Don't bother to paste the stars.

NEXT:
Then go to the data compiling worksheet (in the example you posted this is:
"OVERALL 1965-1967".
Then go to a column of drivers where you have pasted new data. Work with one
race type at a time.
The RidDups macro needs the drivers sorted, in the example workbook they
were not perfectly sorted.
Sort the 3 adjacent columns (rank, driver, wins) according to only the
driver column, decending. To do so, just select (click on) the word 'Rank' at
the top of the chosen list, then go to Data|Sort|"Driver"|Assending.
Excel will select the three columns for you. Note: This may temporarily
discombobulate your formulas, they will be quickly fixed at the end.
After sorting, NEXT:
Select just the drivers in the middle column, all the way down. Click on the
driver's name in the top cell of the list, then press the control/shift/down
keys.
With that selection still darkened, go to Tools|Macros|This Workbook
Only|RidDups|Run.
That will compare each name with the next and delete the duplicates, VERY
quickly.

NOTE: If you get tired of the screen flashing while the macro is running,
modify the code by putting two new

lines in it:
Right after the three 'Dim' statements, type this new line:
Application.ScreenUpdating = False
Then right after the 'End With' statement, type this new line:
Application.ScreenUpdating = True
These lines will make the screen stop flickering and flashing while the code
is running and will speed up excecution. I should wait until you are
comfortable with the operation of the macro first.

After deleting the duplicates using the Macro; NEXT:
Check your list for the few remaining data problems, for instance, there
might or might not be both of each

of these:
BILL DENNIGER
BILL DENNINGER
BILL HEENEY
BILL HENNEY
DON QUACKENBUSH
DON QUAKENBUSH
HURAM LOCKWOOD
HURAN LOCKWOOD
JIM GREASLEY
JIM GRESLEY
JOHN LOGAN
JOHNNY LOGAN
LEON WEISKE
LEON WIESKE

These may all be legitimate, or some may be misspellings/nicknames.
As you can see, the amount of work to fix the list is much less after
running the macro; these are the only potential duplicates left in the list
that I tested it on, as far as i can tell.
Also NOTE: Jim Gresely's name has a space in front it. If it doesn't appear
exactly like that in each winners list on each data-feeding worksheet, then
your 'wins' formula will not be able to find him and he will always show zero
wins.
NEXT:
Fix the formulas in the wins column by finding a good copy of the formula
left over (there will be a bunch of good ones left in the list, just a few
may be messed up or missing) and then drag-copying the good formula up and
down the remaining list.
NEXT:
Data|Sort the entire list again, this time according to Wins|Decending
/Drivers|Acending. This puts the winningest drivers back on top and the tied
drivers in alphabetical order.
NEXT:
Your rank numbers will now be somewhat messed up, no worries, it was already
somewhat messed up to begin with. You may want to give them a true ranking,
taking into account ties. To do this, go to the first driver.

In the example sheet I worked on the Pre-LIM Wins set of data, so I went to
BOB SENNEKER with 38 wins. Select the cell for BOB's rank, cell I6 (EYE SIX,
not Sixteen).
In i6, type the formula:
=IF(K6<K5,COUNTA($J$6:J6),I5)

Read this formula as, {IF This driver's wins are not equal to the previous
driver's wins (both in column K),
THEN Count all drivers to this point (In column J),
ELSE This driver's rank is tied (equal to) the previous driver's rank (In
column eye).}

Note the Dollar-signs in the first term inside the COUNTA function, this
locks all of the formulas in the column to the top cell while the second term
is adjusted by Excel to match where you copy it.
Copy that forumla all the way down the Rank column.
This formula will make the drivers with the same amount of wins tied in
rank, and the next driver with less wins will pick up the correct next number
(in the example worksheet in the PRE-LIMS WINS data table, there are three
drivers with 16 wins who are tied at fourth, the next two drivers have 10
wins and are tied for seventh,
towards the bottom of the list a whole bunch with 2 wins are all tied at
44th, and the 1-win "wild" bunch are tied at 74th.)


Please let us know if any of this helped. Certainly Latham deserves a check
in the was this helpful/did this answer your question check box because he
really did answer your question.

SongBear

"Joe" wrote:

Some back ground. I am compiling stats for my favorite racetrack and have
already started on the project. I have a worksheet for every year, 57 of
them; which totals the wins, Fast qualiferiers and pre-lim wins for that
year. I have a worksheet for every class (such as late model, hobby stock,
super mods) that has run in those 57 years, also totaling wins, FQ and
pre-lims. Then I have a worksheet for TOTAL wins, FQ and pre-lims (as a
driver may have competed in more than 1 class in his career. I have them
working as far as compiling all the stats, the issue is with duplicating
drivers. What I currently do after inputing a years stats, say 1970. I copy
the drivers for wins in the late model class and add them to the late model
win sheet, but some are duplocated as they had one years previous. I do the
same thing copying those from the Late Model class to the Total sheet. Again
MANY are duplicated. Thus after every year I need to go back and delete the
duplicated drivers and is very tim consuming. My question is. Is there some
sort of formula that will recognicze a "new" driver and add him to the list.
I'll admit my excel experience is limited,so i learn in this fly. My workbook
with only 6 years of date is already 3mb. You can see an early version at
this link,
http://www.modernofficeinteriors.com/berlinhistory.html
I'm open to any suggestions someone might have to make this less tim
consuming.
Also, i'm willing to reward the first who can help with a keyboard tray from
our website. thanks.