Can Excel do this?
Discussion
I was wondering if Excel was able to automatically detect if an input to a cell had previously been entered on another spreadsheet or even file? I know it does an autocomplete but wondered if this was able to identify when the same data, in this case a name, had previously been entered?
I am inputting job applications and keep coming across people who have applied previously. It is inconvenient to short list them again when we have already seen them and rejected them. If would be great if as soon as we tried to enter their names again their name popped up or something. Not worth getting software writen but I thought Excel might be avble to do it as I recall MS were trtying to develop this kind of facility within Windows 9are they still?)
Leftie said:
I was wondering if Excel was able to automatically detect if an input to a cell had previously been entered on another spreadsheet or even file? I know it does an autocomplete but wondered if this was able to identify when the same data, in this case a name, had previously been entered?
I am inputting job applications and keep coming across people who have applied previously. It is inconvenient to short list them again when we have already seen them and rejected them. If would be great if as soon as we tried to enter their names again their name popped up or something. Not worth getting software writen but I thought Excel might be avble to do it as I recall MS were trtying to develop this kind of facility within Windows 9are they still?)
If you keep the data all on the same worksheet autocomplete will work just fine for you. If you're dealing with a large bulk of data, why not use Access? Or just use the 'search' function before you add a new record?
leftie, you could do something in excel if you kept a list of all the names you had previously rejected, and then used the excel "vlookup" function to check your new name against the old list.
But you are likely to run into problems with slight differences between the names on the list and the one you are checking against that then might mean you don't catch the re-applicant. All because excel would look for an exact match.
It might work though.
mark
PS . What are the chances of two people with the same name applying?
But you are likely to run into problems with slight differences between the names on the list and the one you are checking against that then might mean you don't catch the re-applicant. All because excel would look for an exact match.
It might work though.
mark
PS . What are the chances of two people with the same name applying?
mark_T said:
leftie, you could do something in excel if you kept a list of all the names you had previously rejected, and then used the excel "vlookup" function to check your new name against the old list.
But you are likely to run into problems with slight differences between the names on the list and the one you are checking against that then might mean you don't catch the re-applicant. All because excel would look for an exact match.
It might work though.
mark
PS . What are the chances of two people with the same name applying?
But you are likely to run into problems with slight differences between the names on the list and the one you are checking against that then might mean you don't catch the re-applicant. All because excel would look for an exact match.
It might work though.
mark
PS . What are the chances of two people with the same name applying?
Quite often, the sneeky barstyewrads get rejected by one branch and we tell them not to bother applying again for 6 months, and then they have to tell us what they have done in the 6 months to enhance their skills. The easy route is to lie to us about having applied 2 weeks ago to a differnt branch and hope we don't notice. We have about 16000 applications a year held held on one PC but in about 80 different files for 20 odd branches, who hold the detailed records of individual applications. We only keep very basic details at the centre (name , DOB and address) but it would be nice to be able to spot the devious little sods without depending on the memory of my PA. At the moment we occasionally merge the files into one sheet, sort them by surname then by DOB and nail the duplicates. Sadly, they then get disciplined or get the sack for being deceiptful if we have employed them. It's a tough life.
I'm guessing, without having actually tried this, but how about:
- Create one master sheet. When it opens, it is set to automatically update itself from all the local spreadsheets.
- File records: name, address, DoB, date of application, branch of application (may calculate this from the name of the source spreadsheet.)
- Then add a some columns for "Duplicate Check" (name, address, DOB, etc.)
The formula in these Dupe Check columns will be along the lines of:
"IF (name in row) = (any name in column) Then (red asterisk) ELSE (blank)"
So if there is a possible dupe, you see a red flag - dupes on name, address and DOB gives three flags, so a very likely dupe.
The key is to get the right formula for (any name in column). Others may know better if VLOOKUP or something similar will work. An alternative might be to do a manual sort by surname first, then DOB, then forename, and check against the entry in the cell immediately below - whilst the second dupe won't show an alert, the first will. A scroll through the page will highlight any suspicious items (full or partial set of alerts) which your secretary can take a closer look at.
Such a system is not infallible (though a bit of interpretation is probably useful - if name and DOB are identical and address has changed, it takes a human to twig that the odds are that person has moved house), but might provide a useful workaround. My concern would be whether you've created enough rows to be sure all the data is being imported from the source spreadsheets.
However, I suspect a simple database, importing data frm the various spreadsheets, might do this better same principal, but you could be sure all information is being imported.
- Create one master sheet. When it opens, it is set to automatically update itself from all the local spreadsheets.
- File records: name, address, DoB, date of application, branch of application (may calculate this from the name of the source spreadsheet.)
- Then add a some columns for "Duplicate Check" (name, address, DOB, etc.)
The formula in these Dupe Check columns will be along the lines of:
"IF (name in row) = (any name in column) Then (red asterisk) ELSE (blank)"
So if there is a possible dupe, you see a red flag - dupes on name, address and DOB gives three flags, so a very likely dupe.
The key is to get the right formula for (any name in column). Others may know better if VLOOKUP or something similar will work. An alternative might be to do a manual sort by surname first, then DOB, then forename, and check against the entry in the cell immediately below - whilst the second dupe won't show an alert, the first will. A scroll through the page will highlight any suspicious items (full or partial set of alerts) which your secretary can take a closer look at.
Such a system is not infallible (though a bit of interpretation is probably useful - if name and DOB are identical and address has changed, it takes a human to twig that the odds are that person has moved house), but might provide a useful workaround. My concern would be whether you've created enough rows to be sure all the data is being imported from the source spreadsheets.
However, I suspect a simple database, importing data frm the various spreadsheets, might do this better same principal, but you could be sure all information is being imported.
HiRich said:
However, I suspect a simple database, importing data frm the various spreadsheets, might do this better same principal, but you could be sure all information is being imported.
I'd agree, I think the application and your requirement for it has grown beyond Excel now. I've seen some huge and very impressive applications created in Excel, but it's not geared towards these tasks, if only takes some sort of failure where the spreadsheet is stored and you could be in a lot of trouble.
In recruitment there will almost definitely be an off-the-shelf package that can deal with your needs too.
I'm sure I can see how to solve your problem. But I'm not going to tell you how.
Perhaps if you visited some of these candidates, and took their head off with a shovel, you might get the satisfaction you desire. For sure they wouldn't be making a repeat application.
Needless to say that I am not surprised that someone with this much control over other peoples lives is unable to spell "deceitful".
Perhaps if you visited some of these candidates, and took their head off with a shovel, you might get the satisfaction you desire. For sure they wouldn't be making a repeat application.
Leftie said:
Sadly, they then get disciplined or get the sack for being deceiptful if we have employed them. It's a tough life.
Needless to say that I am not surprised that someone with this much control over other peoples lives is unable to spell "deceitful".
Edited by dilbert on Monday 9th October 12:55
I use a conditional format for this. The situation I have most use for it when looking at various references (Risks, Issues etc) it is vexing to have the same reference more than once.
These are often spread across sheets.
To get around this:
Name each column with the data of interest (in this case Name). In this example I would have ranges called R_Proj1, R_Proj2, R_PRoj3 etc.
On each sheet have a conditional format which uses the countif function within it. Thus in call A3 there would be the conditional format:
=(COUNTIF(R_Proj1,A3)+countif(R_Proj2,a3)+countif(R_Proj3))>1
This would trigger the text to turn red.
Hope this helps
Miles
These are often spread across sheets.
To get around this:
Name each column with the data of interest (in this case Name). In this example I would have ranges called R_Proj1, R_Proj2, R_PRoj3 etc.
On each sheet have a conditional format which uses the countif function within it. Thus in call A3 there would be the conditional format:
=(COUNTIF(R_Proj1,A3)+countif(R_Proj2,a3)+countif(R_Proj3))>1
This would trigger the text to turn red.
Hope this helps
Miles
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff