[Solved] Filter one sheet to many consistently

Discuss the spreadsheet application

[Solved] Filter one sheet to many consistently

Postby Luxifer » Wed Apr 20, 2011 9:56 pm

I'm new here. I looked around but couldn't find a similar problem so I'll open a new thread
I get a spreadsheet every few days from someone, always in the same format. I need to manipulate and filter it.
What I would like to do is:
Paste the new sheet over top of the old one in my workbook.
Sheet 2 references sheet 1 and then does all the calculations.
Sheet 3-9 are final worksheets, each a subset of the items on sheet 2.

As an example lets say I get data with these columns:

Id, Name, Format, Price, #inStock

I manipulate this to get

Id, Name, Format, Price, #inStock, MyId, MyName, MyPrice

Now, on sheet 3 I just want
MyId, MyName, MyPrice
for all items where #inStock >0 and format =x

On sheet 4 I just want
MyId, MyName, MyPrice
for all items where #inStock >0 and format =y

etc...

In a way that just recalculates and filters every time I paste in a new dataset.
I'm partly there, but things never seem to work like I want. It's a nifty little problem if anyone here has an idea.
From the posts I've ready, someone's able to do this 8-)
Last edited by Hagar Delest on Wed Apr 27, 2011 11:43 pm, edited 1 time in total.
Reason: tagged [Solved].
LibreOffice 3.3 on Fedora 14
Luxifer
 
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: Filter one sheet to many consistantly

Postby JohnSUN-Pensioner » Thu Apr 21, 2011 9:55 am

There are many ways to do this: macro, database, filters, datapilot or formula. Need to clarify - fields MyId, MyName, MyPrice computed automatically? Filled in by hand?
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Filter one sheet to many consistantly

Postby JohnSUN-Pensioner » Sat Apr 23, 2011 8:09 pm

Maybe it helps to understand
http://wmstrong.ru/images/Animation/split%20a%20table%20into%20multiple%20worksheets.gif

Update 11'Sep 17 Unfortunately, wmstrong.ru now belongs to others. Please, try this link
Last edited by JohnSUN-Pensioner on Mon Sep 11, 2017 8:04 pm, edited 1 time in total.
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Filter one sheet to many consistently

Postby Luxifer » Wed Apr 27, 2011 10:14 pm

Sweet, very nicely done. I've never generated a database that way, only made them from scratch.
Thank you very much.
LibreOffice 3.3 on Fedora 14
Luxifer
 
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Postby JohnSUN-Pensioner » Thu Apr 28, 2011 6:56 am

On the "very nicely"- is not very nice. I noticed a few mistakes in the texts.
Do I need to correct them, to bring the cartoon in the Getting Started <Education <Base <Sample Databases? I don't know...
Glad to be helpful!
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Postby Villeroy » Thu Apr 28, 2011 11:54 am

I noticed a few mistakes in the texts.
Do I need to correct them, to bring the cartoon in the Getting Started <Education <Base <Sample Databases?

You may add a list of corrections below the video. I don't see any mistakes.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Postby JohnSUN-Pensioner » Thu Apr 28, 2011 12:18 pm

"Mistakes" - is only slips: missing articles, "Sheet" instead "Sheets" and similar. English is not my native language :oops:
And in fact I forgot to add instructions only: When we get a new regular file, we can rewrite it over Input.ods or reconnect a database to a new table (Edit-Database)
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Postby Villeroy » Thu Apr 28, 2011 6:42 pm

Another solution in this context: Instead of creating many queries with hard coded filter criteria, one may prefer one parameter query which prompts for the criteria. Just replace the 'x' 'y' 'z' criteria with a named parameter :Which_One (no quotes, no space, no special chars). This way you get another table every time you run the query.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Postby JohnSUN-Pensioner » Thu Apr 28, 2011 7:12 pm

Of course! You know it, I know it, other masters know it... My movie is designed for Dummies beginners, which a parameter in query could frighten. Let them make the first step. Then we are explain complex ideas
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Postby Luxifer » Wed May 04, 2011 3:14 pm

One problem I'm having with this is, for some reason my database will only format fields as text. so I'm trying to do a calculation and I can't. I have only one of my fields as Decimal, all the rest are Text[VARCHAR], and I can't change it. Any ideas? In the spreadsheet I've done all numeric columns as Number/General.
LibreOffice 3.3 on Fedora 14
Luxifer
 
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Postby JohnSUN-Pensioner » Wed May 04, 2011 3:47 pm

There are a few assumptions. Difficult to choose the right without a sample of real spreadsheet.
In my example Price and #inStock is decimal
Decimal and Varchar.JPG
Decimal and Varchar.JPG (19.94 KiB) Viewed 6625 times
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: [Solved] Filter one sheet to many consistently

Postby Villeroy » Wed May 04, 2011 3:58 pm

If your column is a mixture of text and numbers, the database treats the column as a text field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Postby Luxifer » Wed May 04, 2011 6:22 pm

I removed all the text (the column heading was all) and selected the column and made sure it was Decimal format. Saved the sheet, went back to the database and still it's text. Do I have to redo the database or something?
LibreOffice 3.3 on Fedora 14
Luxifer
 
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Postby Villeroy » Wed May 04, 2011 7:52 pm

If in doubt, reload the whole application. If that does not help, you may have overlooked some text candidates.

Download and install this extension: download/file.php?id=11048
Close all spreadsheets, menu:Tools>Extensions...[Add], point to the downloaded zip.
Open your sheet, call Tools>AddOns>SpecialCells>Contents and find all types of cells.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Postby Luxifer » Wed May 04, 2011 8:36 pm

That worked. The original file had text down at 104000 row. <sigh> Thank you.
Nice extension, by the way. I'll be using that more.
So, is there any way that I can format my headings? I'm thinking it's easier to NOT put headings in the database and just do my own formatting and link the data below it?
LibreOffice 3.3 on Fedora 14
Luxifer
 
Posts: 5
Joined: Wed Apr 20, 2011 9:39 pm

Re: [Solved] Filter one sheet to many consistently

Postby Villeroy » Tue Sep 06, 2011 4:13 pm

Luxifer wrote:That worked. The original file had text down at 104000 row. <sigh> Thank you.
Nice extension, by the way. I'll be using that more.
So, is there any way that I can format my headings? I'm thinking it's easier to NOT put headings in the database and just do my own formatting and link the data below it?


I know, this answer comes late. But the topic is read by many guests and I linked to it on several occasions without noticing the additional question.
Keep all column labels (headings). You can rename all columns easily in the query design. In the graphical design view that is the "Alias" field. In SQL you append the alias to the column name: SELECT "Field 1" AS "Name", "Field 2" AS "Birth", "Field 3" AS "Address" ...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Filter one sheet to many consistently

Postby gerard24 » Mon Sep 04, 2017 4:12 pm

Video/Gif of 3rd post is no longer available. :(
LibreOffice 6.2.8 on Windows 10
gerard24
Volunteer
 
Posts: 950
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: [Solved] Filter one sheet to many consistently

Postby RoryOF » Mon Sep 04, 2017 4:18 pm

I have changed that link to point to its site; however it doesn't download for me.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29778
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] Filter one sheet to many consistently

Postby JohnSUN-Pensioner » Mon Sep 11, 2017 8:07 pm

I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.4, LibreOffice 5.4.2.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
JohnSUN-Pensioner
Volunteer
 
Posts: 761
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 28 guests