Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index

Hi Graham,

I experimented a little and found the following solution for you:

In added 2 columns before the one with the heading C/M.

In col G I put the following formula:
=IFNA(VLOOKUP($G$13,INDIRECT("B"&(14+E13)&":C$40"), 2, 0),"")

In col E I put:

To get rid of the #N/A in col E I added col F with:

Pull down to row 39

Now hide col E

How does it work:
I build the starting address for the lookup array from the first row with data (in your case row 
14, and I add the increment where the name was found. Doing that makes that each vlookup starts 
after the row where the last name was found.

In column E I determine the relative place where the name was found (starting at row 14). Also 
there I calculate the starting point from what we did before by adding the previous increment to 
row 14 to determine teh start row. If we don't do that we'll end up in a loop when a name appears 
more then once (e.g. Becky fo N/M).

I'll append the file, knowing that it will not appear in the list, but you'll receive it in your 
personal mail.

If people want to see the file, I'LL need some advice on how to get it to nabble...

This is how it looks now (hope that the list doen't mess it up):



1       C/M     Darren  
1       Darren  
2       Barbara 
3       Ken
2       N/M     Barbara 
4       Neil    
5       Catherine       
8       Sheila
3       M       Ken     
9       kie     
6       Alix    
11      Les
4       C/M     Neil    
15      Eric    
7       Brian   

5       N/M     Catherine       

10      Keith   

6       N/M     Alix    

12      Tom     

7       N/M     Brian   

13      Martin  

8       M       Sheila  

14      Ditte   

9       C/M     kie     

16      Becky   

10      N/M     Keith   

17      Judy    

11      M       Les     

18      Mark    

12      N/M     Tom     

19      Becky   

13      N/M     Martin  

20      Steve   

14      N/M     Ditte   

21      Adnan   

15      C/M     Eric    

22      Steve   

16      N/M     Becky   

23      Becky   

17      N/M     Judy    

24      Bell    

18      N/M     Mark    

25      Grace   

19      N/M     Becky   

26      Janine  

20      N/M     Steve   

21      N/M     Adnan   

22      N/M     Steve   

Hope this helps,


On 24 mrt. 2015, at 15:37, IGraham wrote:

If i have a list over a number of columns how would i create a new list of
only certain rows from that list
Yes i know thats as clear as mud so i've attached a example spreadsheet
The column starting at B14 has membership type with (C14) a name next to it
- the actual sheet has more data.
Each row below has different membership type and associated data

I want new lists based on the membership type
The main list may be sorted changing the order of the rows 

Anyone know how i can do  this   
test1.ods <>  


W764  LibreOffice
View this message in context:
Sent from the Users mailing list archive at

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.