Here is my situation.
Column A- Last (Name) - Contains about 1000
Column B- First (Name) - Contains about 1000
Column C- Date of Birth - Contains about 1000
Column E- Last, First - Contains about 200 and is a subset of column A and B
Column D- I created this to be =A2&","&" "&B2 so that it merges columns A and B to be in the format of coumn E
Column F- =VLOOKUP(E2,D2:D1000,3,FALSE)
I did this to try to pull out the DOB's for my subset but am getting a "#REF!" error which makes sense because the actual data in column D is a formula, not "Last, First" data.
I tried just doing "text to column" for column E and then using the VLOOKUP command based on the new Last name only column that was created from the "text to column" and column A.
This worked, however, if there was more than one person with the same last name, it kicks out the DOB for the first match it comes to, i.e. not taking into account the first name and giving me an incorrect DOB.
Does anyone know how I can use the VLOOKUP command to do this?
Or is there another option? A better, easier method?
Thanks!!!
Can you upload example?
"Relax. What is mind? No matter. What is matter? Never mind!"
Here is a screen shot of the first bit of my data. Only provided some because I had to change names, dates, etc for privacy reasons.
http://livelesson.connectionsacademy.com/p32817444/
Cell D2 = A2&","&" "&B2
Cell F2 =VLOOKUP(E2,D2:D1000,3,FALSE)
Column A contains around 1000 Last Names
Column B contains around 1000 First Names
Column C contains around 1000 Date of Births
Column D is a column that I made using the above formula
Column E contains around 200 Last, First Names
Column F is a column that I made trying to pull the DOB's for Column E names from Column C.
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sorry, I am new to this site, how exactly do you attach files? Thanks
Here is the example
Click New Post
Click the paperclip icon and Browse and Upload file
Add a few words in the message area and clikc Post Reply.
Edit... I guess you figured it out!
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
What is it you actually want to do? Just see if the item in column E exists in column D?
If so
=Isnumber(Match(E2,$D$2:$D$1000,0)) will give TRUE if match found, FALSE if not.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Actually I want a column F of Date Of Births which corresponds with the names in column E. And then I want to be able to sort by my new column of birthdays.
Column D is a list of all high school students in my school. Column E is a list of just my students. I want to be able to create a Column F of my students birthdays without having to go through all 1000 high school students and pick out which 200 students are mine and find their birthday.
And then I want to be able to sort the list by my students DOB.
Last edited by ericrminton; 01-25-2010 at 05:04 PM.
Try:
=IF(ISNUMBER(MATCH(E2,$D$2:$D$1000,0)),INDEX($C$2:$C$1000,MATCH(E2,$D$2:$D$1000,0)),"")
copied down.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Well that seemed to work (see attachment) however the numbers that it produced do not relate to actual DOB's that I can tell. If they do, I'm not seeing how.
Also, eventually I'd like to put the DOB's in order that they would appear throughout the calendar year regardless of year of birth.
So eventually I would like them to appear as follows
Name1 ...... 1/5/1994
Name2 ...... 1/7/1992
Name3 ...... 1/9/1995
Name4...... 1/11/1993
Name5 ...... 1/15/1991
Name6 ...... 1/19/1994
etc
or more generally
Name1 ...... 1/5
Name2 ...... 1/7
Name3 ...... 1/9
Name4 ...... 1/11
Name5 ...... 1/15
Name6 ...... 1/19
You need to format that column now as dates.. Format|Cells and in Number tab choose Date and then choose from the Type options...
Then try sorting by Column F
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Ok I figured out how to format them so they appear in the date format, but when I sort by column F they are showing up from youngest to oldest .... which is not exactly how I want to sort.
I would like to sort by birthdate (month and day) regardless of year of birth.
So eventually I would like them to appear as follows
Name1 ...... 1/5/1994
Name2 ...... 1/7/1992
Name3 ...... 1/9/1995
Name4...... 1/11/1993
Name5 ...... 1/15/1991
Name6 ...... 1/19/1994
Right now what I have is
Name5 ...... 1/15/1991
Name2 ...... 1/7/1992
Name4...... 1/11/1993
Name1 ...... 1/5/1994
Name6 ...... 1/19/1994
Name3 ...... 1/9/1995
I think you would need to add another column with formula =DATE(YEAR(TODAY()),MONTH(F2),DAY(F2)) copied down and sort by that column
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Figured it out with the following code
=MONTH(F2)*100+DAY(F2)
And then sort by that column.
NBVC Thanks for all your help!
Now how to I mark it as resolved?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks