Forum Discussion
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 ...
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
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),""))
