Excel/Google Spreadsheet question

Nope, it's not Doug this time! 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

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.

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? I just don't know if there's any way to automatically sort a list or populate a list in a sorted order...

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.

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!



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

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...

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.

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

Yeah, I sometimes spend hours making something nice that would have taken minutes (total) to do the simple way...

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.

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.

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...

Yeah. I have no clue if the ranking formula can be done in google. For sure it can be done in excel.