RE: Help with a formula. Lookup? referance?
- From: JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
- Date: Sat, 27 Jan 2007 16:47:01 -0800
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.
- Prev by Date: Re: cmdbarmenu focus problem
- Next by Date: Re: Date Formulas
- Previous by thread: RE: how to add a value based on a date change in Excel?
- Next by thread: RE: Help with a formula. Lookup? referance?
- Index(es):
Relevant Pages
|
