Any Google Sheets gurus?

General lounge
1

Any Google Sheets gurus handy? trying to sort out a function and use a spreadsheet in ways spreadsheets aught not to be used.

I have a members list for a group which includes province and position in the group. I’d like a cell for each province which will show the one [and only one] person in that province who has given a position.

So on one sheet I have a full member roster. On another I have some data such number of members in each province which I calculate using:

=countif('Full Roster'!E:E, A7)

.
I’d love to also have a cell which would basically be:

if ('Full Roster'!D:D = "position title") and ('Full Roster'!E:E = "this province") 
     then return ('Full Roster'!A:A)

I realize this is asking a lot from a spreadsheet and may not be possible, but maybe it is?

2

The function you are looking for is: LOOKUP

1 Like
3

e.g. LOOKUP(“position title”, LOOKUP(“this province”, ‘Full Roster’!E:E, ‘Full Roster’!D:D), ‘Full Roster’!A:A)

1 Like
4

Thanks for the lead. That doesn’t seem to want to work, but I’ll keep tinkering. Thanks! Half the time the trouble is in knowing what terms to google to find the info you need.

5

I usually am doing similar types of calculations in Google docs. A lot of the time I add a 2nd data sheet to my project the sort then data in stages. So example with your stuff I’d create a column which only shows a name if it meets the criteria otherwise blank, also maybe add extra columns which would group or prioritize the data. So say you have a list of members with positions and your looking for the person with the highest position but that can be different per province I’d add a number that corresponds to the position. Higher number for a higher position. This is a good use for lookup which would use another column for static data.

Then in the next column run a sort of the first column data looking just for person in each province with the highest position. MAX() is what I might use, other functions for other requirements.

This could all be done in one column but combining all these functions into one column of cells creates really, really big functions that are hard to edit.

Most of what Excel uses you can find versions in Google. its the VBA code in Excel that you have to rewrite into JavaScript then use googles scripting functions.

FYI: there are some databases you can add as functions into googles docs that you can then work with in sheets. Its been quite awhile since I’ve done anything that way, but I might have to do that soon

1 Like
6

I’m still messing with this. A pivot table would be perfect, but it wants to fill the pivot table with calculated values rather than cell values.

7

Finally got it sorted in a completely different way. It turns out they have a Query Language based on SQL built into Sheets.[[Documentation here]] so I used QUERY() to get it and figured I’d throw the answer up here for posterity.

Sample Data:

Column A: Real Name
Column B: Forum Name
Column C: Membership Number
Column D: Position
Column E: Region

the function to get what I want is this:

=query(‘Full Roster’!A2:E,“select A where (D contains “position_title” and E contains ‘desired_region’)”)

So you set the range you want to search. In my case row 1 is all headers and safely ignored so all data in columns A thru E, skipping the headers in row 1. A contains the name of the member. If I wanted to return their Member ID, that would be "select C where … "

This looks like an awfully powerful feature, so I look forward to using it more often and figured some others would get some use out of it.

Cheers!

2 Likes