Page 1 of 1
[Solved] Showing last 10 rows based on match
Posted: Thu Jan 10, 2019 9:43 pm
by Kinoss
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.
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 8:39 am
by Zizi64
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?
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 3:38 pm
by Kinoss
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.
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 4:24 pm
by Zizi64
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?
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 7:50 pm
by Kinoss
Yes, if Holm#8219 had commented in the green area there would have to be an additional 10 rows above that as well.
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 8:20 pm
by Zizi64
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.
Re: Showing last 10 rows based on match.
Posted: Fri Jan 11, 2019 9:43 pm
by Kinoss
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