Seek'n'Destroy

[emukiss10]

Hello,

I have 2 sheets with lots of coumns and among them names and surnames.

I like excel to match columns E and F (name, surname) sheet 2 to columns F and G sheet 1 and if match compare column E sheet 1 to column B sheet 2 and if colum E has value 22 and column B sheet 2 do not have string "Partial" in it than remove that row from sheet 2.

the variables are:

22 - Partial
12 - Full
14 - old
21 - Part off
23 - part max

Its tricky

best regards
w.

 

[than_gold]

Hello,
As you said its tricky, could you please provide sample data in excel sheet, so that you will be able to get enough help.

 

[emukiss10]

sHEET 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Case[/TD]
[TD]Name[/TD]
[TD]Surname[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Mike[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21[/TD]
[TD]Terry[/TD]
[TD]Crews[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Jey[/TD]
[TD]Lo[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Ann [/TD]
[TD]Lons[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[TD]Akira[/TD]
[TD]Toriyama[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Joe[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD]Hulk[/TD]
[TD]Hogan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Lester[/TD]
[TD]Bumham[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Adam[/TD]
[TD]Savage[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



SHEET 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]CASE[/TD]
[TD][/TD]
[TD][/TD]
[TD]NAME[/TD]
[TD]SURNAME[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Partial collection[/TD]
[TD][/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]semi Partial[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ann[/TD]
[TD]Lons[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Old reg[/TD]
[TD][/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]Black[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Partial User[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lester[/TD]
[TD]Bumham[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Partial Collection[/TD]
[TD][/TD]
[TD][/TD]
[TD]Adam[/TD]
[TD]Savage[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



In this example, Row 3 Sheet 2 should be removed because Ann Lons do not have "FULL" in CASE column.
Sheet 1 should always be intact.

 

[emukiss10]

Guys, can't it be done? - sorry for spam I just need help.