logo
down
shadow

Excel 2010 Macro to compare two columns for finding the matched value


Excel 2010 Macro to compare two columns for finding the matched value

By : cosgroma
Date : November 18 2020, 11:13 AM
wish helps you Looks like you were almost there. Your copy line needed a little tweaking, however. In the example below, I added an additional variable called rngName, to store the range of the name to be copied and assigned it a value in the for j loop. if the numbers match (i.e. rng1.value = rng2.value) it will copy the range containing the name to the associated row in sheet 2. Notice that I used .Range ("E" & i) for the copy-to range. The copy-to range in your example would always drop the name in the same cell as its always assigned to "E2". Also, you have a variable called TotalRows that had no value. It must have meant something in the original code you copied it from, so I got rid of that too. Try this and let me know how it works for you.
code :
Sub test()
 Dim rng1 As Range, rng2 As Range, rngName As Range, i As Integer, j As Integer
    For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
        Set rng1 = Sheets("Sheet2").Range("B" & i)
        For j = 1 To Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("Sheet1").Range("C" & j)
            Set rngName = Sheets("Sheet1").Range("B" & j)
            If rng1.Value = rng2.Value Then
                rngName.Copy Destination:=Worksheets("Sheet2").Range("E" & i)
            End If

        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
 Next i
End Sub


Share : facebook icon twitter icon
Compare Two Columns in Excel and Output Matched Results

Compare Two Columns in Excel and Output Matched Results


By : Julien M
Date : March 29 2020, 07:55 AM
help you fix your problem Just noticed that you want to use VBA - not too sure why when this can be easily done with built in formulas?
Because you are sort of going backwards using a VLLOKUP might not be the best choice try using INDEX and MATCH ...
Compare two columns in Excel and delete from the second what was matched in the first

Compare two columns in Excel and delete from the second what was matched in the first


By : Kajiri Thames
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I have two columns of values, "A" that contains words only, one word per cell, and Column "B" that contains urls, one url per cell.
code :
Sub ComparePermittedURLS()

    Dim rngDel As Range
    Dim rngFound As Range
    Dim varWord As Variant
    Dim strFirst As String

    With Sheets("permitted_urls")
        For Each varWord In Application.Transpose(.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value)
            If Len(varWord) > 0 Then
                Set rngFound = .Columns("B").Find(varWord, .Cells(.Rows.Count, "B"), xlValues, xlPart)
                If Not rngFound Is Nothing Then
                    strFirst = rngFound.Address
                    Do
                        If Not rngDel Is Nothing Then Set rngDel = Union(rngDel, rngFound) Else Set rngDel = rngFound
                        Set rngFound = .Columns("B").Find(varWord, rngFound, xlValues, xlPart)
                    Loop While rngFound.Address <> strFirst
                End If
            End If
        Next varWord
    End With

    If Not rngDel Is Nothing Then rngDel.Delete

    Set rngDel = Nothing
    Set rngFound = Nothing

End Sub
In Excel 2010 compare data from columns and highlight values if different using macro and VBA

In Excel 2010 compare data from columns and highlight values if different using macro and VBA


By : Ethan Bonin
Date : March 29 2020, 07:55 AM
this will help I have two columns, from 2 worksheets. In the first worksheet a column contains a list of craftspeople assigned to a task. The second worksheet contains just a list of the craftspeople. I need to compare the craftspeople from the first worksheet and highlight a cell if its value does not match any value in the second worksheet. , Try to use following code:
code :
Sub CompareAndHighlight()

    Dim rng1 As Range, rng2 As Range, i As Integer, j As Integer
    Dim isMatch As Boolean

    For i = 2 To Sheets("workorders").Range("U" & Rows.Count).End(xlUp).Row
        isMatch = False
        Set rng1 = Sheets("workorders").Range("U" & i)
        For j = 1 To Sheets("craftspersondata").Range("A" & Rows.Count).End(xlUp).Row
            Set rng2 = Sheets("craftspersondata").Range("A" & j)
            If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
                isMatch = True
                Exit For
            End If
            Set rng2 = Nothing
        Next j

        If Not isMatch Then
            rng1.Interior.Color = RGB(255, 0, 0)
            rng1.Value = "Incorrect Name"
        End If
        Set rng1 = Nothing
    Next i

End Sub
Excel - Compare two columns, if match then subtract the matched data and give the result

Excel - Compare two columns, if match then subtract the matched data and give the result


By : Leigh
Date : March 29 2020, 07:55 AM
Hope that helps I need to subtract the rows from by matching column 1 and column 2. The values of the rows are present in characters. , Looks like your first example is backwards. For the others, use
code :
=SUBSTITUTE(A1,B1,"")
Excel VBA - Compare values in two columns and copy matched row to new sheet

Excel VBA - Compare values in two columns and copy matched row to new sheet


By : Vivek
Date : March 29 2020, 07:55 AM
wish helps you As pointed out by Scott Craner, your updating of lastRowM based on foundTrue is not working. foundTrue isn't really needed, as long as you update lastRowM each time you add a new row to Sheet3. I have kept it in the code in case you do want to display a message if the value isn't found.
Related Posts Related Posts :
  • Excel - VB - Re-ordering /Renaming columns
  • "Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
  • ShowAllData method of Worksheet class failed
  • VBA If cell is under a certain length, highlight and display message
  • VBA: Unable to get the Match property of the WorkSheet function class
  • Highlighting the second and more duplicate rows in excel but not first occurrence?
  • Excel add- in focus not returned to the active sheet
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org