What would the formula be to have newly entered rows automatically copied to the end of another set of columns if a particular value matches?
Example:
Advanced filter? (You can control it manually, or by a macro. And you can launch the macro by an Event)
Thanks, but Advanced Filter will not do.
I need an automated solution whereby when new data is enter in a new row, upon a match, that row is automatically copied and tacked onto the end of another set of columns that contains data from previous rows.
.
So, in the example, when entering new data in row A11, if B11=15, then automatically (not manually) copy that row to E4:G4, and do the same for all other subsequent rows where column B-15.
The description you provide does not fit a formula. You describe (parts of) an algorithm. So you will need to write a macro.
.
Not defined: Where does “particular value” comes from and if this should be a persistent copy or not (values stay in second column-set, even if removed from first).
.
To do it with formulas:
- Use MATCH() to find the first matching row and calculate/store/show the row-number in a hidden column.
- Show the matching values, based on the found row.
- The row below needs a MATCH-formula, wich starts below the row you already found, to show the next match.
- Add IF() around to avoid showingvsomething, when no match is found
- Drag your formulas down as far as you need…
1 Like
To questions of this kind (not exclusively concerning the view) always attach an example file (.ods for Calc) showing what you have and what you want to get.
Even experienced users may need tzo play a bit with an example before they can suggest a good solution.
Attach an example!
And what should happen if you enter date, integer, decimal with an integer other than 15 
thanks for all your replies!
.
Wanderer, the particular value in this example would come from the header F1.
.
Villeroy, nothing should happen if rows entered and the value in column B does not match (in this case “15”).
.
copy new rows if match.ods (15.8 KB)
And what shall happen if B11 is later changed to 17 ?
What if in some row the key number wasn’t entered as 15, but later changed to this value?
What version of LibO are you using? V24.8 or higher?
(Also: What’s your OS?)
Lupp, good question! I could see that would present problems.
.
It is possible but rare that there could be errors entered since the data will be pasted into each row. If B11 was later changed to 17, then it would be best to remove that row and the other rows shifted up to eliminate a blank row.
.
I’m using Windows 10 and Lib 7.1.4.2.
The array function supposed to be helpful (depending on your answers to the other questions) is FILTER(), and was introduced with LibO V24.8.
Again: What about updating or not?
That seems to be a problem. From your screenshot I’d assume F1 (actually E1:G1) gets it value from A2:C2 and is copied as the first match for 15. So you have a circular reference.
Lupp, ok I just upgraded to the latest Lib 25.2.
I tried Standard Filter but it’s not working as expected. Also, now when I attempt to open Libre Help, it crashes…
Wanderer, I added headers in the file I uploaded, and I’m using the reference value in the header in F1.
Did you install the help correctly (same version, UI language)?
Wher did you get the msi-files from?
Lupp, wow, that’s pretty sophisticated, I’ll need to spend some time with it. Thanks a lot!
Install LibrePythonista extension and create dataframe for matching rows from source data.
If you are using the librePythonista extension, you can achieve the desired results with the following two lines of code:
df = lp("A1:C11", headers=True)
df[df.order == 15]
You can easily extend the range to C100 if you need to cover up to 100 rows. Headers can be used as dataframe methods (for e.g. df.order). If the order value is equal to 15, the condition will return TRUE and display relevant rows.
You can try this solution online here:
http://ec2-3-237-174-118.compute-1.amazonaws.com:8000/
To explore further, right-click on cell F2 (name) and select “View as object” or “Edit code” from the Pythonista menu.
ask118477_hsql.odb (13.8 KB)
Open the form.
On the left you enter the filtered items.
The right side shows the full table content.