How to create "Shopping List" with markable Items in Sheet 1 and Final List (without Row Gaps) in Sheet 2?

[Pantona]

Assumed we have this simple kind of table in sheet 1:

Now I want to automatically create a new table in sheet 2 where only the items/their rows marked with an “x” are contained. So when creating a shopping list, I just need to write an “x” to the desired articles within sheet 1 and only those articles will get forwarded into sheet 2. Of course, this would be possible with a simple IF(B3="x",C3,"") request, but in this case there are several empty rows between shopping items in sheet 2 in case of not all items were selected in sheet 1.

How can I let the table appear like this automatically (no empty rows existent anymore)?

Is there some functionality to auto-remove empty rows or paste rows all below each other (defragmented/condensed format)?

Update:

The user @LeroyG has posted a nice approach (thanks!). However, I am not yet sure on how to implement this with two sheets. Furthermore, each shopping list article has more columns than displayed in my example (filled with further data, e.g. manufacturer, price, etc.). This is why I need to “forward” the whole row from sheet 1 into sheet 2.

My problem are the gaps between the rows in the “final list”…

[LeroyG]

Hello @Pantona,
See an answer to similar question: I wish to create a shopping list that only makes visable items selected - #4 by LeroyG.

If do you need to add more info, don’t use Answer, but edit your question and add the new data there. Thanks.

LibreOffice Help on Applying AutoFilter.

[Pantona]

Thank you very much @LeroyG! I have precised my request a bit better. The “problem” is that

1. My upper example does only show a table with two columns, while in reality the table is much wider/more columns per item (manufacturer, price, availability, etc.)

2. The “raw data” list should be located in sheet 1, while the “final list” should be located in sheet 2. When items get marked on sheet 1, only those articles should get forwarded into sheet 2 in condensed/defragmented format (without gaps between the rows).

[JohnSUN]

I have two solutions for such a problem - using a Standard Filter and using a formula. Unfortunately the sample solution is not in English (was prepared 2011), it will take me a while to translate the sheet comments. For now, you can try this Example_Price_Without_Macro_1.ods (58.9 KB) without translation.

Update The same spreadsheet, with comments in English -
Example_Price_Without_Macro_2.ods (80.0 KB)

[Pantona]

Uuuh that is looking nice! Thank you very much for this idea! Your approach is using a formula, right?

As far as I understand correctly…

1. … you provide unique IDs (Отметка) to each item. Each ID is based on the row number.

2. … the column НУЖЕН! is used for the checkmarks

3. … afterwards you are using a an index function that allows to avoid empty rows by using/checking their specific IDs

I am just wondering why you are using sheet1 (Прайс1) and sheet2 (Прайс2) simultaneously? They are likely the same, no? Or is sheet1 just the raw data (that was afterwards copied into sheet 3)?

If I get it correctly, this is exactly what I am looking for!

[JohnSUN]

Try to translate comments to the sheets using the Google translator (I’m just doing this now): Price 1 is generated using a filter (needs updating if the source data has changed), Price 2 is “live”, formulas instantly react to changes in data on the third sheet

[Villeroy]

ShoppingList.odb (17.0 KB)
Enter items to the input form. Check items.
Checked items will be hidden when reloading the form either by the toolbar button (“Refresh”) or when opening the form.