Calc, selecting from a list ? vlookup maybe ?

users
#1

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>

#2

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

#3

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.

#4

IGraham wrote

I want new lists based on the membership type

Anyone know how i can do this

You can simply add a column title to each column (e.g. on B13 Type and C13
Name)
Click on B13 and then on menu Data, Filter, AutoFilter
Click on the down arrow that shows up to the right of Type, uncheck the
"All" box at the bottom of the filter dialog and choose one Type (e.g. C/M)
You can simply use this filter to see each type or you can copy the result
to another sheet or file
Notice that you can not copy to the columns (as in your example) to the
right because some lines are filtered.

Hope this helps.

#5

OK, here is the file.

test1-rj.ods
<http://nabble.documentfoundation.org/file/n4144386/test1-rj.ods>

Found one thing- The array defined in the vlookup should be one more then
the last data row. Otherwise the last nae doesn't show. Didn't figure out
why....

Amyway, hope this gives you someting to go with.

Succes,
Rob.

#6

Hi & thanks for the replys

Brian & Pedro
yes both good and i'd looked at them, both are quite workable

I wanted something that could be shared (dropbox) and worked on by people
that have little knowledge of spreadsheets
But i also wanted to know 'how to do it' the difficult way

Rob
You gave me the difficult way, i can burn out a few brain cells trying to
work out what its doing
Unfortunately the file didn't show in the email
When your replying in the green bar click 'More' and click to upload a file
Regardless of if you gt that working your probably given me enough to get it
working
thanks

#7

thanks Rob
you obviously worked out the upload file routine