Hi
With the standard filter, I can copy using the dropdown, the result to another column etc.
At the same time I wish to delete the items being transferred to new column, to be deleted from the original list.
Is there an option to do this?
I will be using a macro to filter, transfer, and delete.
How do I fit this into the macro. Just need to know where the option is to delete old data.
Thanks
Charles
Filter the range in place.
Copy the filtered range.
Delete entire rows of the filtered range.
Remove filter.
Paste to the target.
P.S. mind any Header row.
1 Like
Hi Villeroy
Thanks for your help. I can do all that, but I cannot get back to the original list, less the items removed. I think my thinking is back to front! anyway I have attached a sheet to show what I am trying to do. Hobby trains, start at one location and move to other locations and pickup items , drop off items, each location will use a slightly different filter. Once I work out how to show the original list less removed etc I can manage. Will need to be a macro, probably record one initially. There will be 2 macros one for picking up, and one for dropping off, each location has slightly different items etc, the macros will cycle thru say 10 locations. I can do all that.
Is using standard filter the best way or is there a better method. My brain (80)has nearly passed the use by date. It is filter, cut, paste, with a macro.
In example railroad freight cars are removed from Location 1 (leaving some there) and added to location 2 which had none.
TestFilter.ods (16.6 KB)
Thankyou again for your help.
Charles
It is nearly impossible to build inventories on spreadsheets, not even simple ones. Not a single professional inventory system uses spreadsheets. The first spreadsheet “Visicalc” has been invented around 1980 as an arithmetic tool, electronic calculators on steroids.
Yes, we can do some most simplistic list keeping on spreadsheets. However, grouped items (cars of moving trains) moving between groups and locations require a relational database at least.
I’m sure that there must be several spreadsheets out there covering your problem. I doubt that any such “solution” is usable by anybody but the creator, who must have spent weeks with coding complex formulas and macro programs.
First of all, you must not split equally structured information into separate lists and then move the information analog to the real world items. A single list can record which item moves to which group at which location at which time.
| Item-ID | Train-ID | Location-ID | Time |
|---|---|---|---|
| A4711 | T08/15 | N6785 | 2024-05-26 12:32:37 |
Constantly tracking these events by simple records about the what, when and where, software has the power to report the location of any item at any time, locations having items at any time or times when which locations had certain items.
1 Like
Hi Villeroy
Thanks for your great explanation.
What software (freeware) would be your recommendation. I will try recommended software. It seems that a new software will be more capable and simpler to use. I will search inventory and see what I can find.
Would appreciate your recommendation.
Thanks
Charles
I guess his recommendation is: Use a database. If from LibreOffice or stand-alone is your choice. LibreOffice includes 2 embedded types to start, but can also connect to MariaDB or Postgres as well as Sqlite. Maybee check the guide for Base under documentation at LibreOffice.org first…
I don’t know any software related to your hobby. Developing a database is far from trivial. Is your hobby about model railways or is it about train spotting in the reals world?
Trains.odb (103.7 KB) (2nd version with minor bugfix and new dummy data)
Alright, here we go again. For the fun of it, I prepared a database draft without knowing much about your hobby. It contains a tiny little bit of macro code, so you need to store the database document in a “trusted location” according the LibreOffice security settings (Tools>Options>Security> button “Macro Security”) unless you turned off macro security altogether. I recommend to turn on highest security level and declare your documents folder (“My Documents” under Windows) as a trusted location but do NOT declare your downloads folder as a safe place. The store documents calling macros in your documents folder or any subfolder therein.
Very important: Store database documents with embedded databases a local drive. Store backup copies on removable drives nor network drives but do not open documents from removable/remote drives.
A database document has 4 sections for the actual database tables, for queries, forms and reports. Ignore everything but the forms. If you need something printable, I’ll add some report(s). The term “forms” is a bit ambiguous. The database document shows a “Forms” section with form documents. Within a form document you may find multiple logical forms where you actually fill in the data. When I write about opening some form, I mean a form document. When I write about editing a form, I mean some part within the opened form document.
We track the movements of cars between engines and places.
Engines pull docked cars until they are undocked at some place (storing track).
Places (storing tracks) keep undocked cars until they are docked to some engine.
The forms container has several forms under “List Keeping”. There is a “MAIN” form and a form named “README” with general instructions how to use database forms in general and the forms attached to this database document in particular.
Introduction to the system with my dummy data:
I filled the 3 inventories (cars, engines, places) with dummy items and assigned each car to some starting location. Finally, I opened the main form and docked cars to one of the engines. Using the form on top of the table grid.
Open the main form, select the beginning of the history which is the last row in the grey table and navigate upwards. You see which car has been parked at which location or docked to which engine. On the right side you see the occupancy of either the docking train or the storing track at the time selected in the history.
You can see that I first added cars 1-5 to “Place 1”, cars 6-10 to “Place 2”.
Then I picked up 3 cars and docked them to “Engine 1”. I docked 2 cars to “Engine 2”.
That’s my history so far.
On top of the main form document, there are 2 editable input forms where you can dock parked cars to engines or undock cars from their engines. The drop-down lists for the cars are restricted to currently parked cars and currently docked cars respectively. Timestamps are added automatically.
Pick an engine and a parked car from the drop-down lists on the left side and hit Enter. This adds a docking event to the list and shows the current occupancy of the train on the right side. For now, you can’t see the updated status of the parking location.
Pick some location and a docked car from the drop-down lists on the right side and hit Enter. This adds a parking event to the list and shows the current occupancy of the parking location on the right side. For now, you can’t see the updated status of the train.
How to fill in your own data
- Open the cars form, select all my dummy cars and delete them according to my instruction in the README form.
- Open the engines form and delete my engines.
- Same with the places.
The events will be deleted automatically with their related inventory items. Forms EVDOCK and EVUNDOCK should be empty now. - Enter names of your own cars, engines and places (storing tracks) into the respective inventory forms. Use short names or tech identifiers you can recall easily. Duplicate names are rejected. After entering a duplicate name of either item, you can’t move away from the edited record.
4.1. On the cars form, assign newly entered cars to some starting location or engine. Cars that are nowhere will not be selectable in the main form drop-downs. The cars form lists nowhere cars in the bottom left section.
Open the main form and add docking and undocking events. This and navigating the history is all you can do with the main form. The displayed data are calculated record sets. You can edit them, just like you can’t edit the calculated result of a spreadsheet formula.
Any correction can be done in any of the “List Keeping” forms where you can find the events directly in the docking/undocking forms. You can find all the events in the inventory forms as well. They are right of the inventory items. When you select an item, you can see and edit all events related to the selected item.
In the list keeping forms you can cheat by moving cars from one location to another location without docking or from one engine to another engine without parking. Model trains can fly.
Hello sswcharlie
Notwithstanding the existing volume of information, I suggest in Calc, have a main database and it will be generated with PivotTables, for each Location 1.
It would work like this, it updates the main Database, which is in a spreadsheet, and in other spreadsheets it would have divisions Location 1, 2, 3 etc, made with Pivot Tables, it would have a macro linked to spreadsheet events, when a Location spreadsheet , if selected the macro already updates the information.
TestFilter_GS.ods (16,9,KB)
Hi Everone
Thanks so much for all the ideas and examples. I have put my learning hat on and going thru them all. And also look closely at what I am trying to do, and do I need?
Thankyou
Charles
2 Likes
The pivot table introduced by @schiavinatto is the most “database-ish” feature Calc can offer. This is the only spreadsheet feature which can generate a table from another table. However, it covers only a small fraction of the possibilities offered by the Structured Query Language (SQL) which is the foundation of every relational database since the 1970ies.
A pivot table calculates the sum/count/average/min/max for each element shown in the row headers and column headers. It is restricted to one source table.
1 Like
As multiply emphasised spredsheets can only be used like “little” databases in exceptional cases.
But such cases seem to exist.
If data keeping and maintenance of real data are actually done with spreadsheets, some database rules should be regarded anyway.
I won’t talk much of normalisation. The most important thing as I see it is to not tamper with the original data in their place. Sorting and filtering should be done with output to “elsewhere” and leave the original data where they are. If it then later gets clear that the spreadsheet solution is no longer maintainable, you have at least the “data base for a database” and can move these data from one container to another.
As explained above the ouput of such a filtering process (in fact the creation of a dichotomy) should write to two new locations if not only the “positive” part is needed.
I made an example doing this with the help of a bit of user code.
See: ask106192_ DichotomyByFilter.ods (40.3 KB)
Writing to sheets different from the source sheet containing the original data, you could do it even with a function (UDF) called from a cell of this sheet. To avoid expectable complications I chose a way passing the needed parameter to a Sub.