Forum Discussion

anne_sofie's avatar
anne_sofie
Copper Contributor
Sep 05, 2023

List duplicates and move entire row

 

Hi, 
Is there a formula in Excel that causes an entire row to move based on a command/formula for a particular cell in a row?

I have a sheet where column A contains a number/code in each cell which is connected to a number in each cell in column B.
I cannot lose this combination. Column D contains the same number as in column B, but is not in the correct order 
(it is duplicate, but not listed next to each other).
Column D through AN contains rows that cannot lose the combination of each other, 
but I want the number in column D to lie/list next to the duplicate in column B, but for the entire row from D-AN to follow. 

To list the duplicate next to each other I have used the following formula, 
but then I lose the combination from column D to AN:

 


 My question is: Is it possible to add something to this formula to make the entire row follow the value in mentioned cell 
(in this case E1 or column E). 

Regards 
Anne-Sofie

 

excel
Formulas and Functions

4 Replies

Sort By
    • anne_sofie's avatar
      anne_sofie
      Copper Contributor
      Sep 05, 2023

      HansVogelaar 

      Of course! Thank you for your reply

      Sub Listduplicates()
      'Updateby Extendoffice 20160613
      Dim rngA As Range
      Set rngA = Range([E1], Cells(Rows.Count, "E").End(xlUp))
      rngA.Offset(0, 1).Columns.Insert
      With rngA.Offset(0, 1)
      .FormulaR1C1 = _
      "=IF(ISNA(MATCH(RC[-1],C[1],0)),"""",INDEX(C[1],MATCH(RC[-1],C[1],0)))"
      .Value = .Value
      End With
      End Sub

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 05, 2023

        anne_sofie 

        Try this:

        Sub SortEm()
    Dim w As Worksheet
    Dim m As Long
    Dim c As String
    Set w = ActiveSheet
    m = w.Range("A" & w.Rows.Count).End(xlUp).Row
    c = Application.TextJoin(",", True, w.Range("A1:A" & m))
    With w.Sort
        .SortFields.Clear
        .SortFields.Add _
            Key:=w.Range("D1"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=CVar(c), _
            DataOption:=xlSortNormal
        .SetRange w.Range("D1:AN" & m)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Resources