Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Dummy - Please Help!
Can anyone tell me how to accomplish the following:
I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the €śAccountName€ť columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#2
|
|||
|
|||
Excel Dummy - Please Help!
An alternate
Sub Test4Ed() Dim sh1 As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim iPos As Long Dim i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") With sh1 iLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row For i = 1 To iLastRow iPos = 0 On Error Resume Next iPos = Application.Match(.Cells(i, "D").Value, _ sh2.Range("D:D"), 0) On Error GoTo 0 If iPos 0 Then sh2.Cells(iPos, "A").Resize(, 3).Copy .Cells(i, "A") End If Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the "AccountName" columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#3
|
|||
|
|||
Excel Dummy - Please Help!
Can we assume that every AccountName in Sheet1 appears in Sheet2? And these
AccountNames are unique? If so, then I'd just use the match and index functions. In sheet1, cell B2: =index(Sheet2!B:B,match(d2,Sheet2!D:D,false)). In sheet1, cell C2: =index(Sheet2!C:C,match(d2,Sheet2!D:D,false)). Copy those formulas to each row of Sheet1. Note that these formulas don't consider whether the existing values match or not; they just pick up the FullName and DisplayName from Sheet2 associated with the AccountName. --Bruce "Ed" wrote: Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the €śAccountName€ť columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#4
|
|||
|
|||
Excel Dummy - Please Help!
|
#5
|
|||
|
|||
Excel Dummy - Please Help!
Hi Bob - Once again thanks for your help... However, when I run the macro it
appears to do exactly what I want to do except it is also modifying the OU column which should not be modified. Can you please assist? Thanks! Ed "Bob Phillips" wrote: An alternate Sub Test4Ed() Dim sh1 As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim iPos As Long Dim i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") With sh1 iLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row For i = 1 To iLastRow iPos = 0 On Error Resume Next iPos = Application.Match(.Cells(i, "D").Value, _ sh2.Range("D:D"), 0) On Error GoTo 0 If iPos 0 Then sh2.Cells(iPos, "A").Resize(, 3).Copy .Cells(i, "A") End If Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the "AccountName" columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#6
|
|||
|
|||
Excel Dummy - Please Help!
Bruce - Sheet2 is a master list of users and contains more accounts then in
Sheet1, so no, the accounts that appear in Sheet1 may not be in Sheet. And if you're asking if the account names in each of the Worksheets are unique, yes. However, they are not unique across Worksheets. Give my answers, do you still recommend using the match and index solution? Thanks! Ed "bpeltzer" wrote: Can we assume that every AccountName in Sheet1 appears in Sheet2? And these AccountNames are unique? If so, then I'd just use the match and index functions. In sheet1, cell B2: =index(Sheet2!B:B,match(d2,Sheet2!D:D,false)). In sheet1, cell C2: =index(Sheet2!C:C,match(d2,Sheet2!D:D,false)). Copy those formulas to each row of Sheet1. Note that these formulas don't consider whether the existing values match or not; they just pick up the FullName and DisplayName from Sheet2 associated with the AccountName. --Bruce "Ed" wrote: Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the €śAccountName€ť columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#7
|
|||
|
|||
Excel Dummy - Please Help!
Thanks Bob!
"Ed" wrote: Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the €śAccountName€ť columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
#8
|
|||
|
|||
Excel Dummy - Please Help!
Perhaps this might work ..
Sample construct at: http://cjoint.com/?lnsz6XiIWy Check_n_Replace_Val_Ed_misc_14112005.xls Assume Sheet1 and Sheet2's structure are identical, in cols A to D, data from row2 down, e.g.: OU Fname Dname Aname 1 FN1 DN1 AN1 2 FN2 DN2 AN2 3 FN3 DN3 AN3 etc whe FName =Full Name DName =Display Name AName =Account Name ---- Make a copy of Sheet1 In this copy, named: Sheet1 (2) --------------- Put in B2, copy across to C2, fill down: =IF(ISNA(MATCH($D2,Sheet2!$D:$D,0)), IF(ISNA(MATCH($D2,Sheet1!$D:$D,0)),"", INDEX(Sheet1!B:B,MATCH($D2,Sheet1!$D:$D,0))), INDEX(Sheet2!B:B,MATCH($D2,Sheet2!$D:$D,0))) Sheet1 (2) will return what you're after .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Ed" wrote in message ... Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the "AccountName" columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. |
#9
|
|||
|
|||
Excel Dummy - Please Help!
Since we're using a copy of Sheet1,
this error trap part shouldn't be necessary: IF(ISNA(MATCH($D2,Sheet1!$D:$D,0)),"", The formula could be simplified: In Sheet1 (2): Put in B2, copy across to C2, fill down: =IF(ISNA(MATCH($D2,Sheet2!$D:$D,0)), INDEX(Sheet1!B:B,MATCH($D2,Sheet1!$D:$D,0)), INDEX(Sheet2!B:B,MATCH($D2,Sheet2!$D:$D,0))) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
|
|||
|
|||
Excel Dummy - Please Help!
Hopefully this is it Ed
Sub Test4Ed() Dim sh1 As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim iPos As Long Dim i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") With sh1 iLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row For i = 1 To iLastRow iPos = 0 On Error Resume Next iPos = Application.Match(.Cells(i, "D").Value, _ sh2.Range("D:D"), 0) On Error GoTo 0 If iPos 0 Then sh2.Cells(iPos, "B").Resize(, 2).Copy .Cells(i, "B") End If Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Hi Bob - Once again thanks for your help... However, when I run the macro it appears to do exactly what I want to do except it is also modifying the OU column which should not be modified. Can you please assist? Thanks! Ed "Bob Phillips" wrote: An alternate Sub Test4Ed() Dim sh1 As Worksheet Dim sh2 As Worksheet Dim iLastRow As Long Dim iPos As Long Dim i As Long Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2") With sh1 iLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row For i = 1 To iLastRow iPos = 0 On Error Resume Next iPos = Application.Match(.Cells(i, "D").Value, _ sh2.Range("D:D"), 0) On Error GoTo 0 If iPos 0 Then sh2.Cells(iPos, "A").Resize(, 3).Copy .Cells(i, "A") End If Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Ed" wrote in message ... Can anyone tell me how to accomplish the following: I have two worksheets with 4 columns (OU, Full Name, Display Name, AccountName, ). The values in the "AccountName" columns are the same in both worksheet, but the values in the other three are different. I need to find a way to search the two worksheets and when the AccountName in both worksheets match, I would like to replace the "Full Name" and "Display Name" values in Worksheet 1 with the values (Full Name and Display Name) from Worksheet 2. BTW - Yesterday two members were kind enough to assist me with this yesterday but the logic I provided them was a bit wrong. Thanks for your assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |