Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > finding and deleting cells from one excel sheet to another
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default finding and deleting cells from one excel sheet to another
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,510
Default finding and deleting cells from one excel sheet to another
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

"Johnny B" wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default finding and deleting cells from one excel sheet to another
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!

"OssieMac" wrote:

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

"Johnny B" wrote:

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,510
Default finding and deleting cells from one excel sheet to another
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 20
Default finding and deleting cells from one excel sheet to another
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
"OssieMac" wrote:

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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,510
Default finding and deleting cells from one excel sheet to another
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

"Johnny B" wrote:

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
"OssieMac" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding Number of cells in a column of other sheet having a specific word in them [email protected] New Users to Excel 5 February 21st 07 02:51 PM
Finding, searching, and comparing cells to another sheet Andrew M. Excel Worksheet Functions 0 January 24th 07 08:00 PM
Finding and Deleting QPapillon New Users to Excel 2 March 14th 06 04:04 AM
Finding/deleting duplicates and merging cells Louise Excel Worksheet Functions 1 January 20th 06 11:36 AM
Finding and Deleting duplicates in a column Brian Excel Worksheet Functions 3 February 5th 05 03:19 PM


All times are GMT +1. The time now is 06:53 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement