+ Reply to Thread
Results 1 to 10 of 10

Excel Dummy - Please Help!

  1. #1
    Ed
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: 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" <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!
    >




  3. #3
    bpeltzer
    Guest

    RE: 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. #4
    Don Guillett
    Guest

    Re: Excel Dummy - Please Help!

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

    >
    >




  5. #5
    Ed
    Guest

    Re: 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" <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!
    > >

    >
    >
    >


  6. #6
    Ed
    Guest

    RE: 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. #7
    Ed
    Guest

    RE: 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. #8
    Max
    Guest

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

    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.




  9. #9
    Max
    Guest

    Re: 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. #10
    Bob Phillips
    Guest

    Re: 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" <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!
    > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1