Is it possible to delete cells rather than rows/columns?

[chrisbelfast]

Is it possible to delete cells rather than rows/columns?

I will have 2 columns of data (peoples names) what im wanting to do is be left with the same 2 columns of data WITHOUT the duplications.

eg

Current Names List......New List of Names
Bloggs, Joe ................Smith, John
Brown, Jim ................Black, Gary
Smith, John

I want it to finish off with:

Current Names List......New List of Names
Bloggs, Joe ................Black, Gary
Brown, Jim

This is just a sample the actual data will have a list of about 120-130 names.

Everywhere ive looked really only talks about deleting the row which doesnt suit this as the data is effectively 2 lists.

Any pointers will be a great help, the code ive been working on is in work and the IT folks have locked down external emails so i cant post what ive attempted (but those attempts didnt work!)

Thanks in advance
Chris

 

[BenMiller]

Each row in Excel is always, always viewed as one piece of data. If you have two lists which are parallel to each other, you won't be able to do anything with them in excel (sort, filter, etc.)

If the "data is effectively 2 lists", why not put them on two worksheets?

If you're trying to compare them to each other, why not put them in the same column and then remove duplicates? Excel retains the first one and deletes the rest, FYI.

 

[chrisbelfast]

The 2 lists are names of staff the first list are of the current staff and the second list is for new staff, I need to filter out only the changes between the 2 lists so that for the staff who are showing up on the current list but not on the new one access can be removed and for the staff on the new list but not the current list access can be given.

 

[Mohammad Basem]

Is it possible to delete cells rather than rows/columns?

Yes, try this (deletes Smith, John and shifts Black, Gary up)

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Range("B2").Delete[/COLOR][/SIZE][/FONT]

 

[chrisbelfast]

The problem is ive 120-130 names to check, if the name in column a is also present in column b I want the name deleted from both columns. If the name isn't there I want it to check the next name

 

[GTO]

Do you want the name just cleared from both cells, or do we want to eliminate empty cells as well?

 

[chrisbelfast]

Just the names i can get it to do a sort at the end to make sure that both are in alpha order

 

[GTO]

This does not check for any odd/extra spaces or anything, so the match must be on-the-money.

For:

Excel Workbook
	A	B
1	Current Names List	New List of Names
2	Bloggs, Joe	Smith, John
3	Brown, Jim	Black, Gary
4	Smith, John
Sheet1

Excel 2010

Option Explicit
    
Sub example()
Dim rngColA As Range
Dim rngColB As Range
Dim Cell    As Range
    
    With Sheet1
        Set rngColA = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        Set rngColB = .Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
        
        For Each Cell In rngColA.Cells
            If Not IsError(Application.Match(Cell.Value, rngColB, 0)) Then
                .Cells(Application.Match(Cell.Value, rngColB, 0) + 1, "B").ClearContents
                Cell.ClearContents
            End If
        Next
    End With
End Sub

Please note that I used the sheet's default CodeName.

Hope that helps,

Mark

 

[Soldinjo]

Here- this is vba code:

Sub NewNames()
'Position yourself in the first cell next to list with new names
Dim Kol As Integer, Raspon As Range
Set Raspon As Selection
Kol = ActiveCell.Column
Range("A2").Offset(0, Kol - 1).Resize(1000, 1).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Raspon.Select
ActiveCell.Select
Do Until ActiveCell.Offset(0, -1).Value = ""
If Application.CountIf(Range("A2").Offset(0, Kol - 3).Resize(1000, 1), ActiveCell.Offset(0, -1).Value) > 0 Then ActiveCell.Offset(0, -1).ClearContents
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Offset(0, Kol - 2).Resize(1000, 1).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

Position your self in the cell next to two lists

Then run this macro

 

[Soldinjo]

Sorry didnt see that you want names deleted from old list
This delets names from both lists

Here- this is vba code:

Sub NewNames()
'Position yourself in the first cell next to list with new names
Dim Kol As Integer, Br As Integer
Dim Raspon As Range
Set Raspon = Selection
Kol = ActiveCell.Column
Br = Application.CountA(Range("A2").Offset(0, Kol - 3).Resize(1000, 1))
Do Until ActiveCell.Offset(0, -1).Value = ""
If Application.CountIf(Range("A2").Offset(0, Kol - 3).Resize(1000, 1), ActiveCell.Offset(0, -1).Value) > 0 Then
For i = 1 To Br
If Range("A1").Offset(i, Kol - 3).Value = ActiveCell.Offset(0, -1).Value Then
Range("A1").Offset(i, Kol - 3).ClearContents
GoTo Slj
End If
Next i
Slj:
ActiveCell.Offset(0, -1).ClearContents
End If
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Offset(0, Kol - 3).Resize(1000, 2).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
Raspon.Select
End Sub

Position your self in the cell next to two lists

Then run this macro