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($C14+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+unsubscr...@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+unsubscr...@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
[libreoffice-users] Calc, selecting from a list ? vlookup maybe ?
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+unsubscr...@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
Re: [libreoffice-users] Calc, selecting from a list ? vlookup maybe ?
At 07:37 24/03/2015 -0700, Graham Lovatt 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 If you want to do this dynamically, so that the sub-list updates automatically from the main list (as is suggested by your mentioning the VLOOKUP() function), the problem is complicated, I think. But if you are prepared to extract the sub-list manually, so that you would need to repeat the process each time you needed an updated sub-list from a modified main list, you can do this fairly easily using the Sort facility. Yes i know that's 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. o Select all the main list range: all the relevant columns and rows. o Go to Data | Sort... . o On the Options tab, tick Range contains column labels or not - as appropriate. o On the Sort Criteria tab, for Sort by, select your membership type column. o OK. o Now select each sub-list range in turn and copy and paste the rows to form your new lists. Since you say the order of entries in the original list does not need to be fixed, it would be possible to omit the final copying stage and use the sorted material in place. This would simplify what you need to do as the original list is edited: you would need just to repeat the sort, without the subsequent copying. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
