Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Comparing 1 value with two other columns Loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing 1 value with two other columns Loop

    Hello,

    I'm trying to create a loop that checks the names in column A with the names in column D and E. If The names in A are different from either D or E and the cells aren't empty I want to duplicate the line. I got the line duplicating, but it's not ignoring empty cells and duplicating anyway. Please help!

    Sub DiffNames()


    'Loop trough rows, if D != E create a new lines




    Application.ScreenUpdating = False


    Dim lngRow As Long
    Dim Number

    With ActiveSheet

    lngRow = .Cells(65536, 1).End(xlUp).Row



    Do

    If IsEmpty(.Cells(lngRow, 4)) = False And _
    IsEmpty(.Cells(lngRow, 5)) = False And _
    .Cells(lngRow, 1) <> .Cells(lngRow, 4) = True Or _
    .Cells(lngRow, 1) <> .Cells(lngRow, 5) = True Then


    .Cells(lngRow, 1).EntireRow.Select
    Selection.EntireRow.Copy
    Selection.EntireRow.Insert Shift:=xlDown



    End If




    lngRow = lngRow - 1
    Loop Until lngRow = 1

    End With


    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    Try :-
    Code:
    Sub MG04Sep02
    Dim n As Long
    n = 1
    While Cells(n, "A") <> ""
      If Not IsEmpty(Cells(n, "D")) And Not IsEmpty(Cells(n, "E")) Then
        If Not Cells(n, "A") = Cells(n, "D") Or Not Cells(n, "A") = Cells(n, "E") Then
            Cells(n, "A").EntireRow.Copy
            Cells(n, "A").EntireRow.Insert Shift:=xlDown
            n = n + 1
        End If
      End If
        n = n + 1
    Wend
    End Sub
    Regards Mick

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    Thanks, but it didn't work. It duplicated every single line with that code

  4. #4
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    Can you show an example of your initial data in "A,D,& E"

  5. #5
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    http://prntscr.com/p1rely basically would only want to duplicate rows with victor in either D or E
    Last edited by I3atnumb3rs; Sep 4th, 2019 at 10:40 AM.

  6. #6
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    But the names can change so shouldn't be specific to Victor.

  7. #7
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    When my code is run on that data the 2nd ,3rd and 5th lines are duplicated , where is that incorrect ???

  8. #8
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    This is what happens when I run your code http://prntscr.com/p1s33e

  9. #9
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    Below is a Test file with your Data.
    Run the code a couple of times , see if its working , then try with your data !!!
    https://app.box.com/s/gy8yacldolx591wzaowx1sen3l03kl9k

  10. #10
    New Member
    Join Date
    Nov 2018
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing 1 value with two other columns Loop

    When I run the code this is what I get, there are a few lines with only the name Victor in one column that don't duplicate: http://prntscr.com/p1sfu8

    I changed my code around a bit and got it to do what I wanted Thanks for your time tho.

    Sub DiffNames()


    'Loop trough rows, if D != E create a new lines




    Application.ScreenUpdating = False


    Columns("D:E").Select
    Selection.Replace What:="", Replacement:="1", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False


    Dim lngRow As Long


    With ActiveSheet

    lngRow = .Cells(65536, 1).End(xlUp).Row



    Do

    If .Cells(lngRow, 1) <> .Cells(lngRow, 4) = True And _
    .Cells(lngRow, 4) <> 1 = True Or _
    .Cells(lngRow, 1) <> .Cells(lngRow, 5) = True And _
    .Cells(lngRow, 5) <> 1 = True Then


    .Cells(lngRow, 1).EntireRow.Select
    Selection.EntireRow.Copy
    Selection.EntireRow.Insert Shift:=xlDown


    End If




    lngRow = lngRow - 1
    Loop Until lngRow = 1

    End With

    Columns("D:E").Select
    Selection.Replace What:="1", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False



    End Sub
    Last edited by I3atnumb3rs; Sep 4th, 2019 at 11:54 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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