Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#355464 - 08/10/2012 14:06 Excel/Google Spreadsheet question
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
Nope, it's not Doug this time! smile Now I'm wondering how to do something in Excel (or, preferable, Google Spreadsheets which uses most of the same formulas, I think).

I'm part of a business networking group (I think I've mentioned that here before). We're encouraged to bring visitors, because it helps out the group AND the visitors we bring. In order to do this, we're starting a program to make people aware of their need to bring visitors.

We start with all the members listed alphabetically, and every time you bring a visitor, your name drops to the bottom of the list. That's pretty much it.

Now, I already have to keep a table of member's names on Y and our weekly meetings on X, and in the table I note if they brought a visitor and how many they brought (the number of visitors doesn't matter).

Given that I have this table already, is there a way to automate this list of members, showing who needs to bring a visitor sooner than others?

Please let me know if I can make this clearer. I know I'm probably not explaining myself very well laugh
_________________________
Matt

Top
#355468 - 08/10/2012 15:40 Re: Excel/Google Spreadsheet question [Re: Dignan]
larry818
old hand

Registered: 01/10/2002
Posts: 1038
Loc: Fullerton, Calif.
Maybe just change the date of the last visitor brought, then sort by that column?

Top
#355481 - 08/10/2012 18:01 Re: Excel/Google Spreadsheet question [Re: larry818]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
Originally Posted By: larry818
Maybe just change the date of the last visitor brought, then sort by that column?

I could do that, but I need to have the order correct. Someone who brought a visitor last week should be lower in the list than someone who brought someone two weeks ago, who should then be lower than someone who hasn't brought one in a month.

I'm trying to think over a couple ways to do this, but I just can't think out the algorithm. I have to figure that it's good that I need a big table already, where I'll have all the members on one axis and all the weeks on another axis, and the cells will all be filled with the number of visitors they brought that week. I'm just not sure how to use that data to create a sorted list in the manner in which I'm trying.
_________________________
Matt

Top
#355483 - 08/10/2012 18:08 Re: Excel/Google Spreadsheet question [Re: Dignan]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
The only thing I've come up with so far (just an algorithm, not any kind of formula because I don't know what I'd use):

I have my 50-52 columns for the weeks of the year, and then at the end I have a column for formulas. A cell in this column would count how many cells to the left it has to go before it hits a number (either a number that's not a zero or any number at all - I probably won't be putting zeroes for every single person who doesn't bring a guest).

This way, I could use those values to create a new sorted list of members.

So I supposed I'd need two things: 1) a formula that counts how many cells to the left it takes before a number is found (and report back how many), and 2) how I create a sort based on those values.

I suppose I could just have a second sheet that showed the members and their values, then sort it manually, but where's the fun in that? smile I just don't know if there's any way to automatically sort a list or populate a list in a sorted order...
_________________________
Matt

Top
#355484 - 08/10/2012 18:12 Re: Excel/Google Spreadsheet question [Re: Dignan]
hybrid8
carpal tunnel

Registered: 12/11/2001
Posts: 7738
Loc: Toronto, CANADA
Why won't the date work as Larry suggested? You can sort forwards or reverse on the date. So if you started out with the same date for everyone and then only updated it when they brought a visitor, sorting on that column would get you exactly what you're after.
_________________________
Bruno
Twisted Melon : Fine Mac OS Software

Top
#355487 - 08/10/2012 18:29 Re: Excel/Google Spreadsheet question [Re: hybrid8]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
I think I kind of understand. You mean, I could create a list of the members in one column, then the date they last brought a visitor in the second column, then sort by that column?

Well...that would just be too easy, damnit!

laugh

Okay, you guys win. Still, I'd kind of love to figure out how to do it my crazy/stupid way, too...
_________________________
Matt

Top
#355488 - 08/10/2012 18:38 Re: Excel/Google Spreadsheet question [Re: Dignan]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
Using the sane, logical, clearly superior method of doing this (the one Larry offered, not mine of course), I was wondering: is there any way to get it to sort dynamically? For example, I change the date on one of the members and it automatically sorts everyone appropriately? I know, it's pretty easy to simply do a quick sort, but I'm lazy and I like making data jump around in spreadsheets... smile
_________________________
Matt

Top
#355489 - 08/10/2012 18:57 Re: Excel/Google Spreadsheet question [Re: Dignan]
larry818
old hand

Registered: 01/10/2002
Posts: 1038
Loc: Fullerton, Calif.
I would think sorting after would be easier. If you made some error in the date, your record would jump away and you'd have to go look for it.

Excel remembers your last sort, so it's only three clicks to get where you want.

Top
#355492 - 08/10/2012 23:57 Re: Excel/Google Spreadsheet question [Re: larry818]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
Yeah, unfortunately Google spreadsheets doesn't. I know it's a woefully inadequate tool, but it's what I have.

I'll just sort the way you suggest. I was just trying to be fancy about it and it's all just too silly smile
_________________________
Matt

Top
#355500 - 09/10/2012 01:29 Re: Excel/Google Spreadsheet question [Re: Dignan]
larry818
old hand

Registered: 01/10/2002
Posts: 1038
Loc: Fullerton, Calif.
Yeah, I sometimes spend hours making something nice that would have taken minutes (total) to do the simple way...

Top
#355503 - 09/10/2012 03:02 Re: Excel/Google Spreadsheet question [Re: larry818]
hybrid8
carpal tunnel

Registered: 12/11/2001
Posts: 7738
Loc: Toronto, CANADA
You can always write a front end UI to a database back end for this. Might take a little bit longer than typing stuff into Excel though. wink
_________________________
Bruno
Twisted Melon : Fine Mac OS Software

Top
#355517 - 09/10/2012 14:55 Re: Excel/Google Spreadsheet question [Re: Dignan]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
Check out this example google spreadsheet

The top of each column is a sort key. Not quite what you need.

What I think you are asking for is a ranking formula that boils down to "Days since hosting a guest". It would need to use the (current date) - (date found by finding the last entry on row and using that cell location to lookup the date of that meeting) basically it's just the same as you would do manually, but written with excel functions. Then sort the table by the value of the ranking formula.
_________________________
Glenn

Top
#355534 - 10/10/2012 10:07 Re: Excel/Google Spreadsheet question [Re: gbeer]
Dignan
carpal tunnel

Registered: 08/03/2000
Posts: 12338
Loc: Sterling, VA
Nice! That's a great way of doing it. I'll have to play around with this...

*edit*
Hmm, my response was based on your description of what I should do. When I go to edit that spreadsheet I don't see anything but the numbers...

I still like your suggestion, I'm just not seeing anything in the spreadsheet...


Edited by Dignan (10/10/2012 10:12)
_________________________
Matt

Top
#355583 - 11/10/2012 00:01 Re: Excel/Google Spreadsheet question [Re: Dignan]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
Yeah. I have no clue if the ranking formula can be done in google. For sure it can be done in excel.
_________________________
Glenn

Top