Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
Finding, searching, and comparing cells to another sheet | Excel Worksheet Functions | |||
Finding and Deleting | New Users to Excel | |||
Finding/deleting duplicates and merging cells | Excel Worksheet Functions | |||
Finding and Deleting duplicates in a column | Excel Worksheet Functions |