Extracting the whole raw that contains a certain text

billstarter · December 15, 2021, 12:26pm

I have to work with reports that have a bunch of links from different website,
every time I have to open a calc file and check each individual link to see what category it belongs to then I will remove it from the list and put it in a new sheet that only have links from that specific category.
this takes me hours of work everyday, so I was wondering if there was a way for me to make liberoffice orgnize the new data automaticlly here is a more easier example of what I need:
this is an organized sheet that shows all the website related to category food:
±-----------------------------+
| FOOD SHEET |
±----------±-----±----±----+
| site | date | png | tag |
±----------±-----±----±----+
| eatcom | null | null | null|
±----------±-----±----±----+
| fruitcom | null | null | null |
±----------±-----±----±----+

this is the unorganized list that have the necessary data I need but all the website are from different categories:

±--------------------------------------+
| General SHEET |
±----------±-----±---------±--------+
| site | date | png | tag |
±----------±-----±---------±--------+
| bmwcom | 2021 | bmwpng | new |
±----------±-----±---------±--------+
| eatcom | 2005 | tacopng | old |
±----------±-----±---------±--------+
| fordcom | 2017 | fordpng | unknown |
±----------±-----±---------±--------+
| fruitcom | 2020 | aplepng | normal |
±----------±-----±---------±--------+

the result should be:
±--------------------------------------+
| car SHEET |
±----------±-----±---------±--------+
| site | date | png | tag |
±----------±-----±---------±--------+
| bmwcom | 2021 | bmwpng | new |
±----------±-----±---------±--------+
| fordcom | 2017 | fordpng | unknown |
±----------±-----±---------±--------+

±--------------------------------------+
| FOOD SHEET |
±----------±-----±---------±--------+
| eatcom | 2005 | tacopng | old |
±----------±-----±---------±--------+
| fruitcom | 2020 | aplepng | normal |
±----------±-----±---------±--------+

note: there is also the problem with the same website name having different type of text example :
eat.com could also be writing in general sheet as www.eat.com or with http or https…

JohnSUN · December 15, 2021, 1:51pm

Perhaps your work will become easier if you add another “Sheet” column to the General SHEET with a drop-down list (Data-Validity) with the names of all target sheets. Then you don’t need to move the row right after checking the site. Just mark it, fill the cell in the “Sheet” column. At the end of the work, a not very complex macro will be able to sort the marked rows into the required sheets.

billstarter · December 16, 2021, 12:38pm

first of all thank you for the time and the effort you put to answer my question.
I actually didn’t get what you mean, can you please put an example so I can have a better understanding for your suggestion. also the general sheet aren’t made by me they are submit to me by other people, so it out of my control. and I can not ask for every person that submits a list to put them in a category.
looking forward for your answer, and have a great day.

JohnSUN · December 16, 2021, 12:57pm

Please see the instructions on the first sheet of this spreadsheet - DemoDropdown.ods (35.9 KB)

Hope this helps to understand the first part of my idea.

mariosv · December 16, 2021, 12:25am

With a list of keys to select what data is for every sheet, I think the attached sample achieve it without a too much complex formula.
ExtractRowFromListWithCondition.ods (16.1 KB)

billstarter · December 16, 2021, 12:51pm

thank you soooo much you are a life saver this will save me hours among hours of time wasted on searching and copying with every new text.
the only problem is that some of the website in the general sheet have names like www.bmw(.)com or https://bmw(.)com instead of bmw(.)com mean while the formula you give me require the text to be exact, if you know a way that works with any value that contain that certain text it would be amazing.
again I wish you an amazing day and I really appreciate the help you give me :).

joshua4 · December 16, 2021, 1:11pm

To decode this a little, look at the functions entered as array formulas (via Ctrl+Shift+Enter) in the function wizard–these are from the yellow region:

What this says, is index (INDEX; get the value at that index) into the mini-table at the left (site, date, etc.) according to the ever-less-smallest (SMALL coupled with the very last ROW-1) entry number for those entries filtered by ISERROR (MATCH is just used to not create an error; it’s multiplied by 0 even; the inner (first) ROW determines the value returned in not an error) from the mini-table that match (MATCH) the ones listed in column O (O:O) going down all the rows where these nested functions have been entered as an array function.

mariosv · December 16, 2021, 3:50pm

It could depend, how is the data in ‘Selec’ list, can you attach a sample file, to see what it’s needed to do.
And if file is the default file format ods or xlsx, with the first we can use regular expressions/wildcards on MATCH() function, with the second only wildcards.

billstarter · December 16, 2021, 10:23pm

here is two example, 1 before data is copied the other should be the result that I am looking for
data needed to be copy.ods (10.9 KB)
how data should look like in the final result.ods (10.9 KB)