finding and deleting cells from one excel sheet to another

[Guest]

My question may be difficult to understand. Here is my situation: I have two
excel sheets, (ex: sheet 1 and sheet 2). Sheet 1 has specific cells i need to
identify on Sheet 2 and remove them from sheet 2.
For example
Sheet 1
cell 1 : John Doe | 1234 fake street | las vegas | nevada
cell 2: Jack Box | 4758 name street | san francisco | california

Sheet 2
cell 1: Jane Doe | 3745 main street | los angeles| california
cell 2 : John Doe | 1234 fake street | las vegas | nevada
cell 3: Jack Box | 4758 name street | san francisco | california

i would need to automatically locate all the cells from sheet 1:
(EX: John Doe | 1234 fake street | las vegas | nevada
Jack Box | 4758 name street | san francisco | california)

and remove them from cell 2,
which in the EXAMPLE would leave sheet 2 with only:
(cell 1: Jane Doe | 3745 main street | los angeles| california)

I would really appreciate if anyone could help me out with this problem.
Thank you very much for your time.

~Johnny B

 

[Guest]

Hi Johnny,

In your example you use cell 1, cell 2, cell 3 etc. Do you mean that all of
the name, address, city, state etc separated by vertical lines is all in one
cell or are they in separate cells on one row and this should really be row
1, row 2 row 3 etc?

Regards,

OssieMac

 

[Guest]

hey OssieMac,

oh yeah im sorry.. i meant row 1, row 2, row 3... etc and the vertical lines
partition the rows into columns

thanks!

 

[Guest]

O.K. the try this.

It inserts a column to left of data on both sheets and concatonates all data
in to one column and then looks it up in the second sheet.

Dim rngeSht1 As Range
Dim rngeSht2 As Range
Dim ClientName
Dim Addr1
Dim City
Dim State
Dim c
Dim NameToFind
Dim Y

Sub Delete_Rows()

Sheets("Sheet1").Select

'Insert a column to left of data on sheet 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht1 = Worksheets("Sheet1").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht1
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c


Sheets("Sheet2").Select

'Insert a column to left of data on sheet 2
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'Set this to a range as column 1 and to include all rows
Set rngeSht2 = Worksheets("Sheet2").Range("A1", Cells(Rows.Count, 1))

'Concatonate all the values in cells and place in one cell
'Each value trimmed of superflourous leading and trailing spaces
For Each c In rngeSht2
ClientName = Trim(c.Offset(0, 1).Range("A1"))
Addr1 = Trim(c.Offset(0, 2).Range("A1"))
City = Trim(c.Offset(0, 3).Range("A1"))
State = Trim(c.Offset(0, 4).Range("A1"))

c.Value = ClientName & Addr1 & City & State

If c.Value = "" Then
Exit For 'Exit when run out of data
End If
Next c

'For each value in sheet 1, find corresponding value
'in sheet 2 and if found, delete entirerow.
For Each c In rngeSht1
If c.Value = "" Then
Exit For 'Exit when run out of data to find
End If

NameToFind = c.Value
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not Y Is Nothing Then 'Y Not Nothing = Found target
Do
Y.EntireRow.Delete
'NOTE: FindNext does not work when a row from the range
'has been deleted. Must repeat full find method
Set Y = rngeSht2.Find(What:=NameToFind, _
LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByColumns _
, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False)
Loop While Not Y Is Nothing
End If

Next c
Sheets("Sheet1").Select
Columns("A:A").Delete
Range("A1").Select
Sheets("Sheet2").Select
Columns("A:A").Delete
Range("A1").Select

Regards,

OssieMac

 

[Guest]

Hi OssieMac,

Thank you so much for your help!.. Unfortunately, I unfamiliar with Excel so
I am having difficulty understanding your instructions. I dont understand
what you mean by Dim rngeSht1 As Range, Dim ClientName ... etc, did you mean
=Dim?

I dont understand waht you mean by

"Insert Shift:=xlToRight
Range("A1").Select 'Set this to a range as column 1 and to include all rows
Set rngeSht1 = Worksheets("Sheet1").Range("A1", Cells(Rows.Count, 1)) "
etc.

i am confused.

Thank you so much for your help!

~Johnny B

 

[Guest]

Hi Johnny,

Sorry about that. You said that you "would need to automatically locate all
the cells from sheet 1" and the only way I know how to do it automatically is
with a macro which it now becomes apparent that you do not understand and it
is too difficult to give you lessons on that here on this forum.

Perhaps you can find someone to help you to copy and paste the macro into
the macro editor and then run the macro. It is a challenging but worthwhile
venture to get into macros so it is worth the effort.

Regards,

OssieMac