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: [email protected]
> 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: [email protected]
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
