Forum Discussion

GeoffreyBH's avatar
GeoffreyBH
Copper Contributor
Apr 27, 2025

Move cells automatically in Column

I have two parallel columns of names, both in alphabetical order. Let's call them A & B.  in A the names are duplicated; in B they are not.  I wish to have a formula to compare B with A and, if they ae the same, take no action.  if the are not the same,, I wish to insert a blank cell in B to move all cells below in Column B down one.  The result should see names in column B aligned with the first cell of A having that name.

Before that I need to mark (maybe colour) any names in B that are not listed in A at all.  They are few, if any, so I can extract them (to C, perhaps) and by hand move up the reminder of B to fill the space.

I would be glad of advice, please.

excel
Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 29, 2025

    Formulas cannot insert rows, that requires a macro:

    Sub MoveData()
    Dim rb As Long
    Dim rc As Long
    Dim ma As Long
    Dim mb As Long
    Application.ScreenUpdating = False
    ' Initialize values
    rc = 2
    ma = Range("A" & Rows.Count).End(xlUp).Row
    mb = Range("B" & Rows.Count).End(xlUp).Row
    ' Move names from column B that do not occur in column A to column C
    For rb = mb To 2 Step -1
        If Application.CountIf(Range("A2:A" & ma), Range("B" & rb)) = 0 Then
            Range("C" & rc).Value = Range("B" & rb).Value
            rc = rc + 1
            Range("B" & rb).Delete Shift:=xlShiftUp
        End If
    Next rb
    ' Insert blanks in column B where needed
    rb = 2
    Do
        If Range("B" & rb).Value <> Range("A" & rb).Value Then
            Range("B" & rb).Insert Shift:=xlShiftDown
        End If
        rb = rb + 1
    Loop Until Range("B" & rb).Value = ""
    Application.ScreenUpdating = True
End Sub

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Apr 29, 2025

      Alternatively you can insert this formula in column C to spill a 'new' column B the way you want and a Column D:

      =LET(a, A1:A10, b, B1:B10,
new_b, MAP(SEQUENCE(ROWS(a)),LAMBDA(n, IF((COUNTIF(TAKE(a,n),INDEX(a,n))=1)*(COUNTIF(b,INDEX(a,n))>0),INDEX(a,n),""))),
new_c, FILTER(b, NOT(ISNUMBER(XMATCH(b,a))),""),
IFERROR(HSTACK(new_b,new_c),""))

       

Resources