Excel Dummy - Please Help!

E

Ed

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!
 
B

Bob Phillips

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)
 
B

bpeltzer

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
 
E

Ed

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
 
E

Ed

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
 
M

Max

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

where:
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 ..
 
M

Max

Since we're using a copy of Sheet1,
this error trap part shouldn't be necessary:
IF(ISNA(MATCH($D2,Sheet1!$D:$D,0)),"",
Click to expand...

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)))
 
B

Bob Phillips

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)
 
Top