Spreadsheet manipulation - lists (1 Viewer)

[dantheman]

Not sure how to ask this general question on Google etc, so thought I would ask here and see if anyone knows how to go about manipulating data in spreadsheets (Googlesheets, although presumably the data could be altered in something else like excel, then copied back in)

Spreadsheet trial

Sheet1 No.,Species,Date Total List,Bob List,Bob as per 'Total List',Bob per formula,Jack List,Jack as per 'Total List',Jack per formula,Henry List,Henry per formula,Celia List,Celia as per Total List Total:,11,10,10,8,8,15,5,5,10,0 1,Tawny Owl,1st Jan,Tawny Owl,Tawny Owl,Tawny Owl,Goldfinch,Tawn...

docs.google.com

(Spreadsheet is editable)

Is there a formula/way to re-order the data in Column I to fit into Column J as per the row the data appears in Column B? (ie change it so that Henry's list matches up to the master list to tick off what he has recorded, complete with blank spaces). I have manually done the first two examples. This will save a lot of time if so ...

Yours hopefully, cheers Dan

 

[Mono]

Just trying to get my head around what you are trying to achieve. So first column is the communal list of all the species that have been seen by anyone from the group, in date order of when they were first spotted. The subsequent columns are for the individual members to record the subset of birds they have seen from the communal list. Is this correct?

Do you need the double entry of birds seen by each person? Currently they are shown twice.

Is the sheet maintained by one person or does each person maintain there own column?

Excel is not a database, but there maybe a way of doing it via lookups and having the individuals pick from a drop down of the communal list. Anything not on the communal list would be then have to be added to that list and thus available for all to add to there individual lists.

 

[dantheman]

Just trying to get my head around what you are trying to achieve. So first column is the communal list of all the species that have been seen by anyone from the group, in date order of when they were first spotted. The subsequent columns are for the individual members to record the subset of birds they have seen from the communal list. Is this correct?

Do you need the double entry of birds seen by each person? Currently they are shown twice.

Is the sheet maintained by one person or does each person maintain there own column?

Excel is not a database, but there maybe a way of doing it via lookups and having the individuals pick from a drop down of the communal list. Anything not on the communal list would be then have to be added to that list and thus available for all to add to there individual lists.

Thanks for looking. Yes, part of the issue may be trying to explain. I think dates are relatively unimportant (but that is how the master list is ordered as opposed to eg alphabetic, and the master list has to stay that way). One person would maintain it all just to keep it simple. The first column 'could' then be deleted in that sheet (it is the initial list of their sightings).

So .. on Henry's list (which was presented as a simple list and copied and pasted in)- Herring Gull needs to change position to J10 to match the row it appears in column A (A7), Tawny Owl to J4 (equivalent to A1) etc etc as I manually did for the first 2 (Bob and Jack).

Dealing with lots of columns of up to 50 entries each could be done manually, but would be very time-consuming with scope for errors. And useful to know for future occasions. However adding just one or two species in the future could be done manually - filling in a gap easier, so eg Henry sees a Rook and a Robin in a weeks time it may just be easier to type them in by hand in the appropriate row ...

EDIT: I've copied into a second sheet. And hidden columns E and G just so it looks a bit simpler - that's what we wish to achieve. Do please feel free to alter/edit if that helps.

 

[njlarsen]

In excel, I would keep the list you call "Bob list" on a different spreadsheet. I would then use a lookup function to get data from that list into the column you call "Bob as per total list". But if you are not familiar with it, it will probably be easier to do things manually.

Secondly, does the name of the bird have to be in each person's list? if you instead use "1" for those species they have seen, it will be very easy to keep the total per person up to date using just a sum function that updates itself. (With keeping it as names in excel, a CountIF function can do the same, but is a little bit more involved to set up).

As I do not use google sheet, I cannot say how much of these methods you can use there.

Niels

 

[THE_FERN]

Not 100% sure this is what you're asking, but see if my paraphrase helps:

• You start with a master list which has all birds seen by anyone ["master"—say A1:A10]
• You then have separate lists, one for each person. These have only the birds that person saw [person "Bob" in B1:B5]
• You then want to create "copies" of the personal lists, with gaps for the birds that person didn't see ["gappy Bob": C1:C10]

In XL it's easy. The lists-with-gaps lists you create filling in a formula. It goes in each row that the master is in. So if master is in col A, rows 1: 10, this formula goes in rows 1:10 against some other col where you want the list to appear. When I refer to "master list" I mean A1:A10 etc as above:

=if(iserror(match(A1, B$1:B$5,0)),"",A1)

...And copy this down for the no. rows in "master". This translates as: try to find A1 [master list entry] in Bob's list [B1:B5]. If you fail (=iserror() = TRUE), put a blank (here = ""). If you succeed, put the master list entry.

[Edit: if you then want to count the no each person saw you can use the "counta()" function against the appropriate columns. These are for XL but I'm sure sheets has similar formulae. Failing that, Libre/Open Office definitely does and it's free and open source]

 

[dantheman]

Not 100% sure this is what you're asking, but see if my paraphrase helps:

• You start with a master list which has all birds seen by anyone ["master"—say A1:A10]
• You then have separate lists, one for each person. These have only the birds that person saw [person "Bob" in B1:B5]
• You then want to create "copies" of the personal lists, with gaps for the birds that person didn't see ["gappy Bob": C1:C10]

In XL it's easy. The lists-with-gaps lists you create filling in a formula. It goes in each row that the master is in. So if master is in col A, rows 1: 10, this formula goes in rows 1:10 against some other col where you want the list to appear. When I refer to "master list" I mean A1:A10 etc as above:

=if(iserror(match(A1, B$1:B$5,0)),"",A1)

...And copy this down for the no. rows in "master". This translates as: try to find A1 [master list entry] in Bob's list [B1:B5]. If you fail (=iserror() = TRUE), put a blank (here = ""). If you succeed, put the master list entry.

[Edit: if you then want to count the no each person saw you can use the "counta()" function against the appropriate columns. These are for XL but I'm sure sheets has similar formulae. Failing that, Libre/Open Office definitely does and it's free and open source]

Thanks, that seems to work. My partner tried it after work last night but couldn't get it to, but we tried it just now and, yes, works in excel, and even better, in googlesheets, so brilliant, thanks for that.

(Didn't manage to get the last 'counta' function bit to work in googlesheets, but to be honest, that isn't a biggie)

 

[THE_FERN]

Thanks, that seems to work. My partner tried it after work last night but couldn't get it to, but we tried it just now and, yes, works in excel, and even better, in googlesheets, so brilliant, thanks for that.

(Didn't manage to get the last 'counta' function bit to work in googlesheets, but to be honest, that isn't a biggie)

In Excel, only difference between count() and counta() is the former counts numbers, the latter text entries too. Unfortunately, Excel will probably decide that a cell with "" in it does have text. So if your "gappy Bob" range is c1:c10, with some entries ="" then you might find counta()=10. There are various ways around this. Instead of "" in the formula above you could substitute something which definitely isn't text like 0 (no surrounding quotes). Alternatively, leave as is and try =countif(c1:c10,"*"). This should count entries with (visible) text. There are other more complex approaches using sumproduct etc.

[Edit: a quick Google suggests sheets has counta() too so everything I've said probably applies there too.

If you're doing this for a group, you could probably share an XL with them from your OneDrive rather than using sheets if you're more comfortable with that]

 

[njlarsen]

In excel, I have used this construct to count non-empty cells

=COUNTIF(K2:K131,"<>"&K160)

Where K160 is an empty cell

Niels

 

[THE_FERN]

Might work here (I don't have XL in front of me). A broad issue is that there are only 3 types of things in XL: nos, text or blanks. Unfortunately, xl's notion of what is a blank is somewhat 'interesting'. In particular, zero length strings of whatever persuasion are not blank. But you can't assign a blank to a cell (we can't say "if(<some condition>, blank...)". So I suspect Niels' formula won't work because "" isn't blank to XL.

These sorts of "features" are what makes XL such 'fun' to work with...

 

[njlarsen]

It did work in my spreadsheet.

Niels

 

[THE_FERN]

It did work in my spreadsheet.

Niels

Great. I should note in addition that although xl's types are as I describe them, the ways the various functions behave is far from consistent (you can see this just by comparing where the various arguments go in different XL functions: the order is completely inconsistent)

 

[dantheman]

Thanks both. I have tried both formulae but neither seem to work for me in googlesheets ... ;-)

I've altered the spreadsheet in post #1 to reflect this - columns G and J (The Fern and njlarsen's methods respectively, sheet 1)

It works in the preceeding columns (manually entered), so perhaps it is seeing the formula in the relevant cells and treating them as a text content?

 

[dantheman]

Actually, just messing around, taking the * out of the "*" seems to give the number of gaps (cells with no text). If there is a way of calculating the remainder that aren't empty (in the case of column G above we're looking for 10 of 15 which contain text if we can take out the 5 empty cells)

=countif(G4:G18,"")

 

[THE_FERN]

Actually, just messing around, taking the * out of the "*" seems to give the number of gaps (cells with no text). If there is a way of calculating the remainder that aren't empty (in the case of column G above we're looking for 10 of 15 which contain text if we can take out the 5 empty cells)

=countif(G4:G18,"")

After a quick Google and without XL, try:

=COUNTIF(c1:c10,"?*")

? Means any single character. * Means multiple characters. So equates to "at least one up to/inc many characters". Will work as long as it's not treating "" as containing a character in this case.

 

[dantheman]

After a quick Google and without XL, try:

=COUNTIF(c1:c10,"?*")

? Means any single character. * Means multiple characters. So equates to "at least one up to/inc many characters". Will work as long as it's not treating "" as containing a character in this case.

Yes!!

Nice one thanks ;-)