Hi!
I have a spreadsheet with a list of names. First name and last name in one cell, in random oder:
Smith John
Sam Doe
Kevin Moore
Holmes A.
etc.
In another sheet I have list of the same names but in order, in separate cells: last name, first name with a workplace symbol
Smith | John | 32AB
Doe | Sam | 32FD
Moore | Kevin | 30MN
Holmes | A. | 30 BN
etc.
I need to introduce the workplace symbol in the first sheet acordingly to names. Earlier names in first sheet were in order and I used VLOOKUP but now it gives wrong data or an error. Can you help me out?
Last edited by dzid; 07-25-2011 at 09:03 AM.
You just need more columns on your sheet2 (columns are free!). In column D you could add a key concatenation formula, starting in D2, then copied down:
=TRIM(B2 & " " & A2)
If there are times the names will be in the wrong order, add a second formula in E2 and copy down:
=TRIM(A2 & " " & B2)
Then back on sheet1 the formula in B2, then copied down, could be something like:
=IF(ISNUMBER(MATCH(TRIM(A2), Sheet2!D:D, 0)), INDEX(Sheet2!A:A, MATCH(TRIM(A2), Sheet2!D:D, 0)), IF(ISNUMBER(MATCH(TRIM(A2), Sheet2!E:E, 0)), INDEX(Sheet2!A:A, MATCH(TRIM(A2), Sheet2!E:E, 0)), "none"))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
thanks, but later that day i came up with my own solution, simmilar to yours:
It utlilizes 2 concatenations of Name & Surname and Surname & Name just as you said and in addition marks cells where misspelling occured.y = Worksheets("NameSkill").Cells(rows.Count, 1).End(xlUp).Row x = Worksheets("OutputData").Cells(rows.Count, 1).End(xlUp).Row Worksheets("OutputData").Activate Range("H1:H" & x).Interior.ColorIndex = 3 Range("A2").Select For r = 1 To x s = ActiveCell.Address For t = 2 To y If Range(s).Value = Worksheets("NameSkill").Range("D" & t) Then Worksheets("NameSkill").Range("C" & t).Copy ActiveSheet.Range(s).Offset(0, 7).PasteSpecial 'Paste:=xlValues Range(s).Select Else If ActiveCell.Value = Worksheets("NameSkill").Range("E" & t) Then Worksheets("NameSkill").Range("C" & t).Copy ActiveSheet.Range(s).Offset(0, 7).PasteSpecial 'Paste:=xlValues Range(s).Select End If End If Next t ActiveCell.Offset(1, 0).Select Next r
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks