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!

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" <Ed@discussions.microsoft.com> wrote in message
news:12DE0346-C1D3-49D8-842C-2656076323C7@microsoft.com...
> 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!
>

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

Bob, Didn't he ask for a dummy?<G>

--
Don Guillett
SalesAid Software
donaldb@281.com
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%23aBoutG6FHA.2896@TK2MSFTNGP10.phx.gbl...
> 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" <Ed@discussions.microsoft.com> wrote in message
> news:12DE0346-C1D3-49D8-842C-2656076323C7@microsoft.com...
>> 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!
>>
>
>

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" <Ed@discussions.microsoft.com> wrote in message
> news:12DE0346-C1D3-49D8-842C-2656076323C7@microsoft.com...
> > 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!
> >
>
>
>

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

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

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ed" <Ed@discussions.microsoft.com> wrote in message
news:12DE0346-C1D3-49D8-842C-2656076323C7@microsoft.com...
> 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.

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

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" <Ed@discussions.microsoft.com> wrote in message
news:537F18BA-72E5-4B00-93B2-9580C47B468F@microsoft.com...
> 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" <Ed@discussions.microsoft.com> wrote in message
> > news:12DE0346-C1D3-49D8-842C-2656076323C7@microsoft.com...
> > > 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!
> > >
> >
> >
> >