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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James - 08 Feb 2008 03:23 GMT

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

Signature

Thank you in advance for your assistance.
James

Reply to this Message

Max - 08 Feb 2008 04:17 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I need help desperately - please
>
[quoted text clipped - 15 lines]
>
> Cheers

Reply to this Message

James - 08 Feb 2008 04:50 GMT

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

Signature

Thank you in advance for your assistance.
James

> In Sheet1,
> Put in B1:
[quoted text clipped - 21 lines]
> >
> > Cheers

Reply to this Message

Max - 08 Feb 2008 06:59 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks max for your prompt assistance.
>
[quoted text clipped - 9 lines]
>
> Cheers

Reply to this Message