Re: [libreoffice-users] Calc, selecting from a list ? vlookup maybe ?

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:
=MATCH(G14,INDIRECT("$C"&14+E13):$C$39,0)+E13

To get rid of the #N/A in col E I added col F with:
=IF(ISNUMBER(E14),E14,"")

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):






C/M     

N/M     

M
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,

Rob.


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 <http://nabble.documentfoundation.org/file/n4144307/test1.ods>  
>
>
>
> -----
> IGraham
>
> W764  LibreOffice 4.2.0.4
> --
> View this message in context: 
> http://nabble.documentfoundation.org/Calc-selecting-from-a-list-vlookup-maybe-tp4144307.html
> Sent from the Users mailing list archive at Nabble.com.
>
> -- 
> To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be deleted


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted