query with formula/macro to find text in worksheet and delete it
 

I need help desperately - please

I have a tab in a spreadsheet (sheet 2) with about 200 rows (one column) of
text. I have another tab (sheet 1) in the same spreadsheet with about 16,000
rows of text.

I need a formula or macro that will do the following:-
If the text in R1C1 of sheet 2 exists and matches text in sheet 1, then
delete or blank out that text in sheet 1. Then move on to R2C1 of sheet 2 and
do the same thing, then R3C1 and so on.

I am thinking a looping macro, but I don't quite know enough about how to
write a macro. If it can be achieved with nesting formulas that's also OK. So
far I have used "MATCH" and "ADDRESS" formulas to give me the exact reference
in sheet 1 for the text from sheet 2 but I am stuck with how to use that
reference in a macro or formula that will delete that exact reference in
sheet 1.

Cheers
--
Thank you in advance for your assistance.
James

query with formula/macro to find text in worksheet and delete it
 

In Sheet1,
Put in B1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0))," ",A1))
Copy down to the last row of data in col A. Col B returns the desired
results. Copy col B and overwrite col A with a paste special as values. Clear
col B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote:
I need help desperately - please

I have a tab in a spreadsheet (sheet 2) with about 200 rows (one column) of
text. I have another tab (sheet 1) in the same spreadsheet with about 16,000
rows of text.

I need a formula or macro that will do the following:-
If the text in R1C1 of sheet 2 exists and matches text in sheet 1, then
delete or blank out that text in sheet 1. Then move on to R2C1 of sheet 2 and
do the same thing, then R3C1 and so on.

I am thinking a looping macro, but I don't quite know enough about how to
write a macro. If it can be achieved with nesting formulas that's also OK. So
far I have used "MATCH" and "ADDRESS" formulas to give me the exact reference
in sheet 1 for the text from sheet 2 but I am stuck with how to use that
reference in a macro or formula that will delete that exact reference in
sheet 1.

Cheers
--
Thank you in advance for your assistance.
James

query with formula/macro to find text in worksheet and delete
 

Thanks max for your prompt assistance.

However, the kind of spreadsheet I have and the data I am "cleaning" will be
re-ocurring on a regular basis and the users of the spreadsheet are not very
excel "savvy".

I really do need a macro or one formula that does the entire action without
the need to copy and paste the results or do anything too manual.

Cheers
--
Thank you in advance for your assistance.
James


"Max" wrote:

In Sheet1,
Put in B1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0))," ",A1))
Copy down to the last row of data in col A. Col B returns the desired
results. Copy col B and overwrite col A with a paste special as values. Clear
col B.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote:
I need help desperately - please

I have a tab in a spreadsheet (sheet 2) with about 200 rows (one column) of
text. I have another tab (sheet 1) in the same spreadsheet with about 16,000
rows of text.

I need a formula or macro that will do the following:-
If the text in R1C1 of sheet 2 exists and matches text in sheet 1, then
delete or blank out that text in sheet 1. Then move on to R2C1 of sheet 2 and
do the same thing, then R3C1 and so on.

I am thinking a looping macro, but I don't quite know enough about how to
write a macro. If it can be achieved with nesting formulas that's also OK. So
far I have used "MATCH" and "ADDRESS" formulas to give me the exact reference
in sheet 1 for the text from sheet 2 but I am stuck with how to use that
reference in a macro or formula that will delete that exact reference in
sheet 1.

Cheers
--
Thank you in advance for your assistance.
James

query with formula/macro to find text in worksheet and delete
 

Well, guess you could record a macro when you carry out the manual steps, or
try posting in .programming if others conversant in vba does not drop by
here
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"James" wrote in message
...
Thanks max for your prompt assistance.

However, the kind of spreadsheet I have and the data I am "cleaning" will
be
re-ocurring on a regular basis and the users of the spreadsheet are not
very
excel "savvy".

I really do need a macro or one formula that does the entire action
without
the need to copy and paste the results or do anything too manual.

Cheers
--
Thank you in advance for your assistance.
James