logo
down
shadow

Use Excel VBA to find column matches & merge based on the values of two other column


Use Excel VBA to find column matches & merge based on the values of two other column

By : BaiQiang
Date : November 20 2020, 01:01 AM
this will help I have a little conundrum here & while there's a few suggestions on the site, there's nothing that quite fits the bill for me. I need to merge some rows based on the values of some cells in the row. , The following code should address your conditions. Tested Working.
code :
Sub RemoveDups()

Dim CurRow As Long, LastRow As Long, SrchRng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("A1:C" & LastRow).Select
    Sheets(1).Sort.SortFields.Clear
    Sheets(1).Sort.SortFields.Add Key:=Range("B2:B" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheets(1).Sort.SortFields.Add Key:=Range("C2:C" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets(1).Sort
        .SetRange Range("A1:C" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

For CurRow = LastRow To 2 Step -1
    If Range("C" & CurRow).Value = "Present" Then
        If CurRow <> 2 Then
            If Not Range("B2:B" & CurRow - 1).Find(Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) is Nothing Then
                Range("C" & CurRow).Value = "New Starter"
            End If
        End If
    ElseIf Range("C" & CurRow).Value = "New Starter" Then
        Range("C" & CurRow).EntireRow.Delete xlShiftUp
    End If
Next CurRow

End Sub


Share : facebook icon twitter icon
Excel - How to find strings in Column B based on multiple rows in Column A and return the corresponding row in Column C?

Excel - How to find strings in Column B based on multiple rows in Column A and return the corresponding row in Column C?


By : GTDevo
Date : March 29 2020, 07:55 AM
like below fixes the issue I would advise against VBA when you can achieve the required results easily with worksheet formulas
Put the following formula into cell D1 and drag down
code :
{=IF(SUM(IFERROR(FIND($A$1:$A$4,$B1),0)),$C1,"")}
Merge multiple rows based on column & sum time values in Excel for MAC

Merge multiple rows based on column & sum time values in Excel for MAC


By : user45354
Date : March 29 2020, 07:55 AM
wish helps you Assuming your data is in Column A:D and result needs to be displayed from Column G,
To get all the unique Names from Column A enter the following formula in Cell G2
code :
=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($G$1:G1,$A$2:$A$9),0,0),0)),"")
=VLOOKUP(G2,A2:D9,2)
or
=INDEX($B$2:$B$9,MATCH(G2,$A$2:$A$9,0))
=SUMIFS($D$2:$D$9,$A$2:$A$9,G2,$C$2:$C$9,"<>LP CA Meal Penalty")
or
=SUMPRODUCT(($D$2:$D$9)*($A$2:$A$9=G2)*($C$2:$C$9<>"LP CA Meal Penalty"))
=SUMIFS($D$2:$D$9,$A$2:$A$9,G2,$C$2:$C$9,"<>LP CA Meal Penalty",$C$2:$C$9,"<>LP CA Rest Break Penalty")
or
=SUMPRODUCT(($D$2:$D$9)*($A$2:$A$9=G2)*($C$2:$C$9<>"LP CA Meal Penalty")*($C$2:$C$9<>"LP CA Rest Break Penalty"))
Excel VBA find values in another column if it matches

Excel VBA find values in another column if it matches


By : Jaelyen
Date : March 29 2020, 07:55 AM
hop of those help? Generally, my problem is I want to find values in Column B if it Exists in Column D and do concatenate the first character in Column D to Column B. , The code below will do what you want.
code :
Option Explicit

Sub AddMatch()
    ' 23 Dec 2017

    Dim Ws As Worksheet
    Dim Rng As Range                        ' the range to search in
    Dim Fnd As Range
    Dim Rl As Long                          ' last used row
    Dim R As Long

    Set Ws = ActiveSheet
    Application.ScreenUpdating = False
    With Ws
        ' determine last row in column B
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        ' set the search range in column D, starting in row 2
        Set Rng = Range(.Cells(2, 4), .Cells(Rl, 4))
        For R = 2 To Rl                     ' start in row 2
            If XlFind(Fnd, Rng, .Cells(R, 2).Value, LookAt:=xlPart) Then
                .Cells(R, "C").Value = .Cells(R, "B").Value & " " & Left(Fnd.Value, 2)
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Function XlFind(Fnd As Range, _
                Where As Range, _
                ByVal What As Variant, _
                Optional ByVal LookIn As Variant = xlValues, _
                Optional ByVal LookAt As Long = xlWhole, _
                Optional ByVal SearchBy As Long = xlByColumns, _
                Optional ByVal StartAfter As Long, _
                Optional ByVal Direction As Long = xlNext, _
                Optional ByVal MatchCase As Boolean = False, _
                Optional ByVal MatchByte As Boolean = False, _
                Optional ByVal MatchPosition As Long, _
                Optional ByVal After As Range, _
                Optional ByVal FindFormat As Boolean = False) As Boolean
    ' 09 Dec 2017
    ' Fnd is a return range
    ' Settings LookIn, LookAt, SearchOrder, and MatchByte
    ' are saved each time the Find method is used

    Dim Search As Range
    Dim FirstFnd As Range

    Set Search = Where
    With Search
        If After Is Nothing Then
            If StartAfter Then
                StartAfter = WorksheetFunction.Min(StartAfter, .Cells.Count)
            Else
                StartAfter = .Cells.Count
            End If
            Set After = .Cells(StartAfter)
        End If

        If MatchPosition > 1 Then LookAt = xlPart
        Set Fnd = .Find(What:=What, After:=After, _
                        LookIn:=LookIn, LookAt:=LookAt, _
                        SearchOrder:=SearchBy, SearchDirection:=Direction, _
                        MatchCase:=MatchCase, MatchByte:=MatchByte, _
                        SearchFormat:=FindFormat)
        If Not Fnd Is Nothing Then
            Set FirstFnd = Fnd
            Do
                If MatchPosition Then
                    If InStr(1, Fnd.Value, What, vbTextCompare) = MatchPosition Then
                        Exit Do
                    Else
                        Set Fnd = .FindNext(Fnd)
                    End If
                Else
                    Exit Do
                End If
            Loop While Not (Fnd Is Nothing) And Not (Fnd Is FirstFnd)
        End If
    End With

    XlFind = Not (Fnd Is Nothing)
End Function
How to merge/copy a single column's values in one sheet into another sheet based on the values of a column using excel?

How to merge/copy a single column's values in one sheet into another sheet based on the values of a column using excel?


By : AndrewL
Date : March 29 2020, 07:55 AM
With these it helps In sheet A, cell C2 :
=INDEX('Sheet B'!$B$2:$B$4,MATCH('Sheet A'!A2,'Sheet B'!$A$2:$A$4,0))
Excel - Macro to merge values into one column, based on unique repeated field

Excel - Macro to merge values into one column, based on unique repeated field


By : Dave Grant
Date : March 29 2020, 07:55 AM
will be helpful for those in need If it has to be done in macro: Google returned several options here's one. VBA Conditional concatenate of a group of cells in two columns however this can be done without a macro in a few easy steps and formula's... Here's how
In this first step I setup values like you have I ten had D=A and in E entered the formula listed
Related Posts Related Posts :
  • 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
  • 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
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #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