Calc - sort in to columns

users
#1

Hi

Calc

I've been scratching my head, racking my brain and grinding my teeth - non
of which worked

How do I sort out from a mixed list of membership types the email addressers
into separate columns without gaps or 'false' or '0' appearing
I've attached a simple mock up to make sense of my garbled explanation -
hope it makes sense anyway

Thanks for any solutions test2.ods
<http://nabble.documentfoundation.org/file/n4098035/test2.ods>

#2

I'm guessing from your question and sample spreadsheet that by "sort out"
you wish to have the email addresses appear in a separate column. If
correct the following may not be suitable but...

Use a filter on the columns. It will present a list of entries in your
members table showing only the "member type" the list was filtered by. Copy
the filtered list of email address to wherever you need them.

To filter...
1) select the entire member table, including the column headings
2) from the menu select "Data | Filter | AutoFilter", column headings will
now have a small downward arrowhead at their right hand edge
3) click the arrowhead to the right of "member type"
4) check the box next to each member type you wish to see in the filtered
list
5) click OK

The member list is now displayed filtered by the member type(s) you chose.
The emails can be copy/pasted wherever else you need to use them.

You should be aware
- the entire member list must be selected to begin, step 1 above, or you
won't get all the members you expect in your filtered list

-Alan

#3

Hello and thanks to Alan & Brian (who replied private)

Yes my choice of words to describe the problem wasn't great, 'sort' was the
first thing I tried so was probably the first to swim up from the murk.

The mock sheet shows a problem from a larger sheet saved as a .xls, stored
within Dropbox and accessed and added to by other people - all of whom have
limited spreadsheet knowledge. Anything I add to the sheet has to add
minimum chance of giving a user the possibility of screwing things up.
Which was why I was keen to present the member type emails in separate
columns to just be used.

So the first thing I tried was 'Auto filter', that worked but the user still
had to select the relevant from up to 60 rows (not a big problem I suppose).
However if a user sorted the sheet and subsequently saved the sheet then the
next user would be presented with a sheet they hadn't seen before, do a bit
of a panic and possibly try to add data in the wrong place. I understand
filters, they 'might' but best not to wear rose tinted glasses.

I also tried 'If' statements. Consternation (include a 'index column +
member type = unique) and VLOOKUP. Neither of which gave good result.

I was trying to set something up that wouldn't mean extra and ongoing work
for me, but maybe just maintaining a simple text file is a way to go.
Possibly this whole 'member details' sheet would now be better served by a
database. But I'm not great at database stuff and I don't know what my users
would make of it.

If you have any other solutions/thoughts I'd be grateful (theres shed loads
of spreadsheet stuff (I know) I don't know)

#4

Just tried something that *may* work for you: pivot tables.

On your sample spreadsheet, I did the following:

* Select all the data, header row included.
* Select "Data | Pivot Table | Create".
* With "Current selection" selected, click "OK".
* Drag "member type" and "email" to "Row Fields".
* Click "OK".

A new sheet is created with the pivot table, which has the data sorted
by member type, with all the email addresses listed per type.

Note that if data is added, it won't automatically show up in the pivot
table. You can right-click in the pivot table and select "Edit Layout",
then click "More" and change the "Selection from" value to include the
new rows of data. If you set the last row to be beyond the end of the
current data, you will get a new "member type" in the pivot table
called "(empty)", but any data now added in empty rows that are still
within the pivot table selection will show up if you right-click on the
pivot table and select "Refresh".

This isn't really what pivot tables are for, and may not suit your
needs, but given what you've described of your needs, this may be a
sufficient workaround.

You haven't really given us a proper explanation of what you are trying
to do, so it's hard to guess what might suit your needs best, but it
does sound like a database would be the best solution here, or perhaps
user defined functions (i.e. coded macros) in Calc. Short of that,
I think that you'll probably need to manually maintain a list of
email addresses per member type, either in a separate file, or still
manually in your columns as per the example file. Even using a pivot
table, you'll have to either adjust the selection when data is added,
or make the selection bigger than the data and just refresh when data
is added, and hope the data doesn't go beyond the end of the selection,
in which case you'll still need to adjust the selection.

Maybe if you give us more details we can come up with better
suggestions.

Hope this helped.

Paul

#5

Hi

Nice try Paul
I've never used pivit tables so didn't know they could produce that, which
as you say may be a sufficient workaround, I could use it and it gives the
closest result to what I want I've tried. But really the more I think about
it the more I know a simple text file will be the easiest to maintain and
for the users to use.
I'm still toying with the database idea, but in truth that would be more to
satisfy my own urge to relearn and tinker.

Thanks for looking at it