|
Post by moree on May 21, 2013 23:44:27 GMT
Have you ever been stuck on the correct way to enter an Excel function? Or have you ever wanted to do something in Excel and said to yourself “Surely Excel must have a function for that”?
Well there are thousands of blogs, forums and articles on the web that could help you but, instead of wading through hundreds of results returned by Google you can get most, if not all, the help you need right there in Excel.
Here's how ...
(but if you're still stuck for an answer you can post your question here and we'll be happy to help. Be sure to tell us what version you are using and post pictures to make it easier for us to visualise the problem)
When you click on "Help on this function", this is what you will see
After you paste into your own worksheet ....
As with everything in MS Office there are several ways to achieve the same ends. Here is how you would do it from the Formulas ribbon
|
|
|
Post by brian on May 30, 2013 9:00:49 GMT
OK. here's one. I want to compare two lists of names and numbers in Excel. The problem is they are not both in the same order. so in the first list I might have 1 joe bloggs 2 fred smith 3 4 jim jones
and in the second 1 joe bloggs 2 3 john doe 4 jim jones etc. I want to know which are present in both, which are in 1 but not 2, and which are in 2 but not 1. The numbers are in column 1, the names in column 2.
|
|
|
Post by moree on May 30, 2013 10:26:28 GMT
Is it the combination o number and name that you need to test for? For example, if you have 1 Joe Bloggs in the first list and 4 Joe blogs in the second list Is that a match or not? Here is an example of comparing the lists based on names using vlookups. I've also added the number associated with the name if a match is found using match/index The vlookup for the first list is =IFERROR(VLOOKUP(B2,$B$11:$B$14,1,FALSE),"No Match") for the second list =IFERROR(VLOOKUP(B11,$B$2:$B$5,1,FALSE),"No Match") The match/indexes for the lists are =IF(D2="No Match","",INDEX($A$11:$B$14,MATCH(D2,$B$11:$B$14,0),1)) =IF(D11="No Match","",INDEX($A$2:$B$5,MATCH(D11,$B$2:$B$5,0),1)) If you need to key on the combination of number and name - this can be done as well by concatenating them in a new column in each list and then vlookup on the new columns.
|
|
|
Post by brian on May 30, 2013 16:42:58 GMT
Sorry I should have made clear, any particular name will always be with the same number. So joe bloggs will always be 1 if joe bloggs remains in the second list. There will be names in the first list which are not in the second and vice versa. I'm trying to compare two membership lists to see who has dropped out and who is new.
|
|
|
Post by moree on May 30, 2013 20:35:20 GMT
That's what I figured. My first suggestion will work. Assuming that the first list is the base then if you get a "No Match" from the vlookups in the first list that would equate to a "Dropped Member" so you can change the "No Match" to "Dropped Member". If you get a "No Match" from the vlookups in the second list that would indicate a "New Member" The vlookups for the lists are; =IF(ISERROR(VLOOKUP(B2,$B$11:$B$14,1,FALSE)),"Dropped Member","") =IF(ISERROR(VLOOKUP(B11,$B$2:$B$5,1,FALSE)),"New Member","") If you send me the layout of your lists (sheet names and column/rows) I can help you adapt the vlookups to your situation.
|
|
|
Post by brian on May 31, 2013 8:15:48 GMT
I'll give that a go, thanks very much. One hiccup, the new list (2) does not actually have the unused numbers, so it's not just a case of looking across columns on the same row. So it actually is
1 joe bloggs 3 john doe 4 jim jones
The problem comes from two different people working on this, using different formats. The new list is a search from an access database. I'll try and get a shortened version on a worksheet and forward it. The actual one has some 3000 entries.
|
|
|
Post by moree on May 31, 2013 8:55:43 GMT
If the number and the name are in different columns then there is no issue since the lookups do not use the number column at all. So in your example it will still find Fred Smith as a dropped member.
If the number and name are in one cell it is easy enough to separate them. Let me know if this is the case.
|
|
|
Post by brian on May 31, 2013 12:15:11 GMT
No, number and name are in different cells. So the numbers are immaterial. That makes it easier. If I put the numbers in column A, the first set of names in columb B, the second set of numbers in C, and second names in D........
|
|
|
Post by moree on May 31, 2013 12:39:38 GMT
It depends on what your end game is. Do you want to create a new consolidated list in excel? Will the new list be imported into access? Will you be re-numbering the names?
The best way to do this will depend on where you are going with this.
|
|
|
Post by brian on Jun 1, 2013 8:07:40 GMT
Your questions made me think more about this, which is good. This will stay in Excel, no requirement to import back to access. Actually, my main objective is to find which names in list 1 are not in list 2. There is other detail I require this for. In some cases there will be a different name against the same number in list 2, because the number has been reallocated when the name in list 1 dropped out. List two is the current difinitive list. However it would also be useful to highlight the new names in list 2.
If we had a web based list on access, life would be easier as I would not need to go through all this, but that's not possible at the moment.
Maybe if I explain the whole setup briefly. I run a forum for a club. This is open to everyone, but some sections are restricted to club members. I keep a list on excel of club members and their forum names, and I need to be able to find who has dropped out of the club so that I can restrict their forum membership.
|
|
|
Post by moree on Jun 1, 2013 19:10:56 GMT
Hi Brian I've created a file for you to download (it's been a while since I've tried this so I'm hoping it still works) www.screencast.com/t/Z9tplMkMSCCopy and paste your first list into columns A and B and the second list into Columns G and H starting in Row 3. It's important NOT to paste anything into columns C and D and J as these contain the formulae. Also, I've set this up for a maximum of 400 members so if you have more let me know. What you should see is something like this. Give this a try and if something screws up just let me know and I'll try to fix it.
|
|
|
Post by brian on Jun 1, 2013 22:05:23 GMT
Give that person a Gold Star! Yes it works, thanks very much. It still has to be checked manually because I now see that names have been entered in a slightly different way in some occasions. For instance in one list an extra initial is used, in the other it's not. No formula can cope with that, but it only occurs now and then, so no problem. I need it to cope with 3000 entries though.
|
|
|
Post by moree on Jun 1, 2013 22:52:55 GMT
I've extended to a max of 10,000. I can see a slight delay in response which seemed acceptable to me but, depending on your system specs and your patience threshold, may be too much for you. If the response lag gets really bad we'll need to convert to a Macro to get the results. Give this a try and see if it's ok. www.screencast.com/t/ML3WoIr3EB
|
|
|
Post by moree on Jun 1, 2013 23:02:50 GMT
It still has to be checked manually because I now see that names have been entered in a slightly different way in some occasions. For instance in one list an extra initial is used, in the other it's not. No formula can cope with that, but it only occurs now and then, so no problem. If you have a unique user name or screen name or even a mailing address that you can add in that would solve the problem.
|
|
|
Post by brian on Jun 2, 2013 7:58:40 GMT
I don't have that information for all members, I can live with what you have produced so far. Is there a way to copy the results in the status column without copying the formula?
|
|