Match cell content from another sheet & delete automatically

Discuss the spreadsheet application

Match cell content from another sheet & delete automatically

Postby Dr.Calculus » Mon Jan 20, 2020 1:54 pm

I have two sheets with data. I want to match the cell contents in my no2 sheet to the respective in sheet no1, and then have those cell contents that have been matched in sheet no1 be deleted automatically. How can I do that, instead of comparing one by one and deleting them manually?
OpenOffice 4.1.7 on Windows 10
Dr.Calculus
 
Posts: 2
Joined: Mon Jan 20, 2020 1:49 pm

Re: Match cell content from another sheet & delete automatic

Postby Lupp » Mon Jan 20, 2020 2:25 pm

Welcome to the forum!

To be able to try an answer I need more information.

Question1 in return (I'm not a native speaker of English): How should "... to the respective..." be interpreted here?
All Sheet1 cells of same content?
Same cell address in Sheet1?
Otherwise? How?

Quesion2 in return:
Shall only (constant) content be matched or also formula results?

Question3 in return:
Can you (approximately) describe the "size" of the problem? Solutions probably working for a few thousand cells may be too inefficient for much more.

(Depending on your answers additional questions in return may be necessary. You may better describe wat you eventually try to achieve.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2918
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Match cell content from another sheet & delete automatic

Postby Dr.Calculus » Wed Jan 22, 2020 12:35 pm

So in sheet 1 i have 2 columns: the 1st contains specific numbers while the 2nd contains text. The sheet 1 contains more than 3000 entries.
In sheet 2 now again I have 2 columns: the 1st contains text and the 2nd specific numbers that can be found in the respective column in sheet 1. These are approx. 100 entries.

What I want to do is delete automatically the text that corresponds with the 100 specific numbers (of sheet 2) in sheet 1, without having to manually search-and-find each entry separately (while comparing with sheet 1).
OpenOffice 4.1.7 on Windows 10
Dr.Calculus
 
Posts: 2
Joined: Mon Jan 20, 2020 1:49 pm

Re: Match cell content from another sheet & delete automatic

Postby Villeroy » Wed Jan 22, 2020 12:51 pm

=ISNUMBER(MATCH($A1;$Sheet2.$B$1:$B$100;0))
returns TRUE if this row's value in A ($A1) has an exact match in Sheet2.B1:B100. Expand the formula downwards, filter the list by TRUE values in this column, delete entire rows, remove filter.

A spreadsheet is not a database no matter how many users try to use it as a database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests