Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup or Match
Worksheet 1 = Output
Worksheet 2 = Lookup I want to ensure that the names in worksheet 1 are the same as worksheet 2. Is there any code to do this "crazybass2" wrote: I am not clear as to what you have/want....what I think you have is 2 workbooks, one with sheet names "Fred Bloggs" and "Richard Smith" and another workbook with sheet names "Mr Fred Harry Bloggs" and "Mr Richard John Smith" and you want the second workbooks sheet names to match the first. Is this correct? If this is the case, is it always the same pattern (ie. remove the prefix and the middle name)? Mike "mhd143" wrote: Not sure how to go about this, but what i'm trying to do is that from an output worksheet i want to ensure the names column matches the lookup worksheet names. I then want the names in the output worksheet replaced with the entry from the lookup worksheet. OUTPUT(before) Mr Fred Harry Bloggs Mr Richard John Smith LOOKUP Fred Bloggs Richard Smith OUTPUT(after) Fred Bloggs Richard Smith Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup or Match
Insert the following code into a module in the template workbook (the book
with the sheet titles you want). I put a measure in the code to handle multiple sheets for the same name (ie. Mr Fred Bloggs and Mr Fred Harry Bloggs). If you don't want it use the second code below. Mike Option Explicit Sub UpdateSheetNames() Dim This As Workbook, That As Workbook Dim shthis As Worksheet, shthat As Worksheet, ws As Worksheet Dim first As String, last As String Dim i As Integer Set This = ThisWorkbook Set That = Workbooks.Open("P:\Excel Help\MatchSheets2.xls") For Each shthis In This.Worksheets i = 1 first = Left(shthis.Name, InStr(1, shthis.Name, " ") - 1) last = Right(shthis.Name, Len(shthis.Name) - _ InStr(1, shthis.Name, " ")) For Each shthat In That.Worksheets If InStr(1, shthat.Name, first) < 0 And _ InStr(1, shthat.Name, last) < 0 And _ InStr(1, shthat.Name, first) < InStr(1, shthat.Name, last) Then On Error Resume Next MsgBox (first & " " & last) Set ws = That.Sheets(first & " " & last) On Error GoTo 0 If ws Is Nothing Then shthat.Name = shthis.Name Else shthat.Name = shthis.Name & i i = i + 1 Set ws = Nothing End If End If Next shthat Next shthis End Sub '****** END OF CODE ****** '**** SECOND CODE **** Option Explicit Sub UpdateSheetNames() Dim This As Workbook, That As Workbook Dim shthis As Worksheet, shthat As Worksheet, ws As Worksheet Dim first As String, last As String Dim i As Integer Set This = ThisWorkbook Set That = Workbooks.Open("P:\Excel Help\MatchSheets2.xls") For Each shthis In This.Worksheets i = 1 first = Left(shthis.Name, InStr(1, shthis.Name, " ") - 1) last = Right(shthis.Name, Len(shthis.Name) - _ InStr(1, shthis.Name, " ")) For Each shthat In That.Worksheets If InStr(1, shthat.Name, first) < 0 And _ InStr(1, shthat.Name, last) < 0 And _ InStr(1, shthat.Name, first) < InStr(1, shthat.Name, last) Then shthat.Name = shthis.Name End If End If Next shthat Next shthis End Sub '****** END OF CODE ****** "mhd143" wrote: Worksheet 1 = Output Worksheet 2 = Lookup I want to ensure that the names in worksheet 1 are the same as worksheet 2. Is there any code to do this "crazybass2" wrote: I am not clear as to what you have/want....what I think you have is 2 workbooks, one with sheet names "Fred Bloggs" and "Richard Smith" and another workbook with sheet names "Mr Fred Harry Bloggs" and "Mr Richard John Smith" and you want the second workbooks sheet names to match the first. Is this correct? If this is the case, is it always the same pattern (ie. remove the prefix and the middle name)? Mike "mhd143" wrote: Not sure how to go about this, but what i'm trying to do is that from an output worksheet i want to ensure the names column matches the lookup worksheet names. I then want the names in the output worksheet replaced with the entry from the lookup worksheet. OUTPUT(before) Mr Fred Harry Bloggs Mr Richard John Smith LOOKUP Fred Bloggs Richard Smith OUTPUT(after) Fred Bloggs Richard Smith Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
H and V lookup or match? | Excel Worksheet Functions | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
LookUp/Match | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
match or lookup? | Excel Worksheet Functions |