[Solved] Showing last 10 rows based on match

Discuss the spreadsheet application

[Solved] Showing last 10 rows based on match

Postby Kinoss » Thu Jan 10, 2019 9:43 pm

I have a chat log that I've imported into Calc. Column A shows usernames. What I want to do is hide all the rows that don't include specific names. That part is easy enough for me to do, but I also want it to show the last 10 rows above each result. I'm having difficulty understanding how to accomplish this later part.
Last edited by Kinoss on Mon Jan 21, 2019 9:56 pm, edited 1 time in total.
OpenOffice 4.1.4 on Windows 10
Kinoss
 
Posts: 4
Joined: Thu Jan 10, 2019 9:35 pm

Re: Showing last 10 rows based on match.

Postby Zizi64 » Fri Jan 11, 2019 8:39 am

That part is easy enough for me to do, but I also want it to show the last 10 rows above each result.

Are there empty cells between the user names??? Or what is the content of the 10 rows??

Can you upload an .ods type sample file here?
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Showing last 10 rows based on match.

Postby Kinoss » Fri Jan 11, 2019 3:38 pm

I've uploaded an image. The red row would be the matched data. The green rows are the 10 above I'd want to keep showing, while all other rows would be hidden. The logs are hundreds of thousands of rows long spread across multiple documents. Which is why I'm trying to trim the bulk of irrelevant data and make it easier to search.
Attachments
Capture.PNG
OpenOffice 4.1.4 on Windows 10
Kinoss
 
Posts: 4
Joined: Thu Jan 10, 2019 9:35 pm

Re: Showing last 10 rows based on match.

Postby Zizi64 » Fri Jan 11, 2019 4:24 pm

There are repeated names i the list.

And what about when the Holm#8219 is repeated in the appeared 10 rows? It is necessary to show the next previous 10 rows too?
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Showing last 10 rows based on match.

Postby Kinoss » Fri Jan 11, 2019 7:50 pm

Yes, if Holm#8219 had commented in the green area there would have to be an additional 10 rows above that as well.
OpenOffice 4.1.4 on Windows 10
Kinoss
 
Posts: 4
Joined: Thu Jan 10, 2019 9:35 pm

Re: Showing last 10 rows based on match.

Postby Zizi64 » Fri Jan 11, 2019 8:20 pm

Yes, if Holm#8219 had commented in the green area there would have to be an additional 10 rows above that as well.

I think, you must write (WRITE, but not RECORD) a macro for this task.

Please upload a real .ods type sample file here. Delete the sensitive data from it.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7831
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Showing last 10 rows based on match.

Postby Kinoss » Fri Jan 11, 2019 9:43 pm

Actually, I may have found a round about way of doing it. I used this formula:

=IF(OR(A1="ExampleName1";A1="ExampleName2";A1="ExampleName3";A1="etc...");11;B2-1)

What this does is looks for any matching names listed in the formula. If true, then set the cell to 11. Otherwise, it will set the value to one minus the next cell's value.

Then I just use a standard data filter to show only the cells that are greater than 0.

I've attached an example sheet. All that is required to see it in action is to run the data filter on column C.

For any google bots:
How to show or hide rows around a match by X number
Show X number of rows above or below
Attachments
ExampleSheet.ods
(13.25 KiB) Downloaded 6 times
OpenOffice 4.1.4 on Windows 10
Kinoss
 
Posts: 4
Joined: Thu Jan 10, 2019 9:35 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 36 guests