Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
Find text - worksheet function or macro? | Excel Discussion (Misc queries) | |||
find last record in macro and delete all after | Excel Discussion (Misc queries) | |||
FIND A TEXT IN A WORKSHEET NOT IN A COLUMN OR ROW BY FORMULA | Excel Discussion (Misc queries) | |||
Macro to find and delete rows! | Excel Discussion (Misc queries) |