+ Reply to Thread
Results 1 to 11 of 11

Need to compare 2 columns and align them next to the match

Hybrid View

naenightlucky213 Need to compare 2 columns and... 05-07-2013, 01:08 PM
HaHoBe Re: Help!! Simple Script 05-07-2013, 01:25 PM
naenightlucky213 Re: Help!! Simple Script 05-07-2013, 07:47 PM
kalak Re: Need to compare 2 columns... 05-08-2013, 12:45 AM
kalak Re: Need to compare 2 columns... 05-08-2013, 01:27 AM
ducky_yeng Re: Need to compare 2 columns... 05-09-2013, 06:14 AM
  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    austin texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Need to compare 2 columns and align them next to the match

    Using Excel 2007. I need a Macro script that will take my columns B-J and match the value of B to the value of A and move the entire row with B to match A.


    Example Before
    A B C D ...
    1
    2 7 ox green
    3
    4 3 cat brn
    5 1 dog red
    6 4 bird blue
    7 5 rat black


    Example After
    A B C D ...
    1 1 dog red
    2
    3 3 cat brn
    4 4 bird blue
    5 5 rat black
    6
    7 7 ox green

    Thank You in advance!!!
    Last edited by naenightlucky213; 05-07-2013 at 07:46 PM. Reason: Title

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Help!! Simple Script

    Hi, naenightlucky213,

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    austin texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Help!! Simple Script

    Done
    Thank you

  4. #4
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Need to compare 2 columns and align them next to the match

    This code retains your existing formats, not sure if you wanted this, also works if numbers in ColA are not consecutive, not sure if you wanted this either.
    Code is simpler if these 2 conditions are not required.
    Sub test()
    Dim d As Object, sh As Worksheet
    Dim a As Range, b As Range
    Dim j As Long
    Set d = CreateObject("scripting.dictionary")
    Set sh = ActiveSheet
    Set a = sh.Cells(1).CurrentRegion
    n = a.Rows.Count
    For j = 2 To n
        d(a(j, 1).Value) = j
    Next j
    With Sheets.Add
        a.Copy .Cells(1)
        a.Offset(1, 1).Clear
        Set b = .Cells(1).CurrentRegion
        For j = 2 To n
            If Len(b(j, 2)) > 0 Then b.Offset(, 1).Rows(j).Copy sh.Cells(d(b(j, 2).Value), 2)
         Next j
    Application.DisplayAlerts = False
        .Delete
    Application.DisplayAlerts = True
    End With
    End Sub

  5. #5
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Need to compare 2 columns and align them next to the match

    on second thoughts ...
    scripting dictionary is quite fun, but doesn't (didn't) work on a Mac, and not everyone likes it.
    you could instead try
    Sub other()
    Dim n As Long, m As Long, c
    With Cells(1).CurrentRegion
    n = .Rows.Count
    m = .Columns.Count
    .Rows(2).Resize(n + 2, m - 1).Offset(, 1).Insert xlDown
    For Each c In .Offset(n + 3).Resize(, 1).Offset(, 1)
        If Len(c) > 0 Then
            On Error Resume Next
            c.Resize(, m - 1).Cut _
                Cells(Application.Match(c, .Resize(, 1), 0), 2)
            On Error GoTo 0
        End If
    Next c
    End With
    End Sub

  6. #6
    Registered User
    Join Date
    05-07-2013
    Location
    austin texas
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Need to compare 2 columns and align them next to the match

    Hello,
    That almost worked, but it moved a lot of the loans that did have a match all the way to the bottom below everything else. I need them all to match up. Columns B-T need to move together and match up to B's match on A's column. They are numbers in both column A & B. Some will be in one and not the other.

  7. #7
    Registered User
    Join Date
    11-09-2009
    Location
    london, england
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Need to compare 2 columns and align them next to the match

    Wonder if the last piece of code by Kalak has worked as I was working on a similar issue and found that I used a different approach.
    At a high level the process is
    - create a temporary worksheet to hold the results
    - using the data worksheet use vlookup iteratively on each row to match A in B
    - if a match exists then iteratively use vlookup across the remaining columns
    - put he results from the vlookup into the temporary worksheet
    - rename the temporary worksheet

    I chose this approach for my piece of work because I had such a headache with shifting rows up and down, accidentally overwriting data or not clearing cells.
    Below is the attached code and modified to work with your sample dataset that you provided with your first post.
    I'm not sure if this is the most efficient method in terms of speed with regards to dictionary vs vlookup, but I guess is depends the size of your dataset.

    If you have already found another solution then that is great.

    Public Sub MatchIt()
    
        Dim wkData As Worksheet
        Dim wkTemp As Worksheet
        Dim iLastCol As Integer
        Dim iLastRow As Integer
        Dim iHeaderRow As Integer
        Dim iRowCounter As Integer
        Dim iColCounter As Integer
        Dim sLookupRange As String
        Dim vKey As Variant
        Dim result As Variant
        
        iHeaderRow = 1
        sLookupRange = "B:D"
        iRowCounter = iHeaderRow + 1
        
        'get the data work sheet
        Set wkData = Worksheets("Data")
        
        'get the last column
        iLastCol = wkData.Cells.Find("*", SearchOrder:=xlByColumns, _
        LookIn:=xlValues, SearchDirection:=xlPrevious).Column
        
        'get last row
        iLastRow = wkData.Range("A" & Rows.Count).End(xlUp).Row
        
        'create a temporary sheet
        Set wkTemp = Worksheets.Add
        
        'copy the header across
        wkData.Rows(1).Copy
        wkTemp.Range("A1").PasteSpecial (xlPasteValues)
        
        'iterate through the rows doing a vlookup
        'copy corresponding / vlookup corresponding from data to temp data worksheet
        While iRowCounter <= iLastRow
        
            vKey = wkData.Cells(iRowCounter, 1).Value
            result = vlookupVBA(vKey, wkData, sLookupRange, 1)
            wkTemp.Cells(iRowCounter, 1) = vKey
            
            'loop through remaining columns when matching key
            If result <> "#N/A" Then
                iColCounter = 1
                While iColCounter < iLastCol
                    wkTemp.Cells(iRowCounter, iColCounter + 1) = vlookupVBA(vKey, wkData, sLookupRange, iColCounter)
                    iColCounter = iColCounter + 1
                Wend
            End If
                    
            iRowCounter = iRowCounter + 1
            
        Wend
        
        'delete the data worksheet and rename the temp worksheet
        
        
    End Sub
    
    'copied from forum and modified to include the worksheet by ref
    Function vlookupVBA(lookupValue As Variant, ByRef wk As Worksheet, rangeString As String, colOffset As Integer)
    
        vlookupVBA = "#N/A"
        
        On Error Resume Next
        
        Dim table_lookup As Range
        
        Set table_lookup = wk.Range(rangeString)
        vlookupVBA = Application.WorksheetFunction.VLookup(lookupValue, table_lookup, colOffset, False)
        
    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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