[Solved] Find data based on specific criteria

Discuss the spreadsheet application
Post Reply
jjh036
Posts: 3
Joined: Mon Jun 18, 2018 2:48 am

[Solved] Find data based on specific criteria

Post by jjh036 »

Hello,
I am new to OpenOffice and struggling to find a replacement for a macro I would run in Excel and was wanting to know if anyone could at least help with a starting point or other options as I am trying to make a complete switch from Microsoft Office to Open Office. I have been starting to learn Python and I've read that Python can be integrated with Open Office so curious if that would be a better option.

I am trying to copy specific data between two cells and paste them on another sheet. I provided an attachment that has example data.
Attachments
Untitled 1.ods
(17.75 KiB) Downloaded 80 times
Last edited by MrProgrammer on Sun Dec 27, 2020 10:56 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Apache OpenOffice 4.1.5
Windows 10
FJCC
Moderator
Posts: 9231
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calc Options for Finding Data based on specific criteria

Post by FJCC »

A good resource for learning OpenOffice macros is http://www.pitonyak.org/oo.php. The Macros Explained and the English Macro Document are the two you will want to look at. The main difficulty in learning macros is API, which will present much the same problems in OO BASIC or Python. Python is a far better language than OO BASIC but BASIC is what is used in the documents linked above.

The attached document contains the beginnings of a macro. It searches for the first pair of "first" "second" markers and copies the data between them to Sheet2. I hope that helps.
Attachments
SearchMacro.ods
(19.58 KiB) Downloaded 87 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Calc Options for Finding Data based on specific criteria

Post by MrProgrammer »

jjh036 wrote:I am trying to copy specific data between two cells and paste them on another sheet.
You can do this without an evil macro. Macros are useful, but expect to spend a week or more writing your first one, and longer if you're not already a programmer. Instead you can copy the data with a formula.

Insert → Names → Define → Name → _ → Assigned to →
OFFSET(Sheet1.$A$1;MATCH("first";Sheet1.$A$1:$A$99;0);0;
MATCH("second";Sheet1.$A$1:$A$99;0)-MATCH("first";Sheet1.$A$1:$A$99;0)-1;1)
Add → OK. This 131-character formula is to be entered in a single line in the dialog box; I've split it here for readability. You can use two copy/paste operations to put the pieces of the formula into the dialog from this post. You only need to use Insert → Names → Define once. Yes, the name you're assigning is the underscore character.

Type =_ in a cell of Sheet2 and press ⇑⌘Enter on a Mac or Ctrl+Shift+Enter on other platforms. If you type =_ and press Enter instead of ⇑⌘Enter or Ctrl+Shift+Enter you will not get the proper result. You must immediately use Edit → Undo and try again with ⇑⌘Enter or Ctrl+Shift+Enter. Unless you use Edit → Undo after making a mistake you are unlikely to get the formula to work as intended.

This formula will copy the desired data to Sheet2. Finally, replace the formula with its value using
Edit → Copy
Edit → Paste Special → Paste All (uncheck) Text → Numbers → Date & Time → OK

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc Options for Finding Data based on specific criteria

Post by Villeroy »

Just like MS Excel, Calc has an "advanced filter". I think it is even more advanced than in Excel.

I applied the following modifications to your document:
1. Insert a column header on top of your list. There should be one label on top of every list column.
2. Data>Define... I gave the name "List" to the list range.
3. Created a criteria range in D1:D3 with the same column header and two criteria below which is interpreted as "Column Header" equals "first" OR "second"
4. Gave a name "Criteria_01" to the criteria range via Insert>Names>Define... and flagged that range as a range of filter criteria (below "Options")
5. Named Sheet2.A1 as "Target_01"
--------------------------------------------
Usint this set up
Click anywhere in the list range.
Call Data>Filter>Advanced Filter...
Filter criteria from: Criteria_01
More Options:
Copy output to: Target_01
[X] Keep filter criteria
[OK]
-------------------------------------------
Usint this set up again
After adding or removing some first|second entries in the source list:
Click anywhere in the list and call Data>Refresh
This will apply the same filter criteria with the same extra options (copy output to Target_01).
-------------------------------------------
Usint this set up again with changed filter criteria or different criteria range:
Click anywhere in the list range.
Call Data>Filter>Advanced Filter...
Filter criteria from: Criteria_01 or another named filter range or some range address.
[OK]

-------------------------------------------
The Base component provides much more convenient tools to filter and sort lists by predifined or variable criteria.
-------------------------------------------
Attachments
Advanced_Filter.ods
(19.71 KiB) Downloaded 67 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jjh036
Posts: 3
Joined: Mon Jun 18, 2018 2:48 am

Re: Calc Options for Finding Data based on specific criteria

Post by jjh036 »

Thank you everyone. I appreciate the responses, especially with multiple options at my disposal. I will be trying each solution and will confirm once the issue is solved. Thanks again!
Apache OpenOffice 4.1.5
Windows 10
Post Reply