Spreadsheet that’s cross platform w/ 5 level sort

[PaperQueen]

I need a spreadsheet program that can:

1. Operate cross platform (my IT guy’s PC, I’m Mac)
2. Sort capabilities to five, ideally six, levels
3. Capable of using macros
4. Can convert spreadsheets for an html (online) environment

I founded a national grassroots movement that’s gone viral. We list registered supporters on our site, using Google Apps forms, the copy the content into an Excel spreadsheet. A friend out of state (PC user) is able to work magic with macros that magically sorts, fixes, and converts the lists to appear in our online directory (you can see an example at http://www.the350project.net/states/states_a-d.html). I created the graphics; he somehow “builds” the page using those plus the spreadsheets.

This is something I really need to be able to do on my end, as the owner of the site, but MS took macros out of the Mac version of Office Excel....OpenOffice only sorts three levels, and he says we need at least five....

Since I have no intention of becoming a PC user, I need to find a software program that will work on a Mac and still be able to do all this. I realize many (most?) software programs are capable of opening and saving .xls files, but the multi-level sort, macros, and html are critical to any of this working, according to him.

Suggestions? Help? Someone I should talk to?

Thanks in advance, folks. Much appreciated!

 

[exegete77]

Howdy. Since the requirement is “macros” I assume that means VBA, which is what is discontinued on Mac Excel. However, you could use AppleScript to achieve the same results. But if he means that whatever you use has to use VBA, then your only option is to use Bootcamp/Fusion/Parallels and use Windows with Excel 2007 for Windows. (Office 2004 for Mac has limited VBA, based on VB5, the same as Office 97 for Windows).

You could contact Jim McGimpsey, or Jim Gordon, who are both Microsoft MVP for Mac Office.

 

[exegete77]

Or if you want to go on the top-end of the software: Aabel.

 

[PaperQueen]

Thanks a TON, exegete77-—you’re a Godsend.

While he’s been calling them macros, it does appear he’s actually referring to VBA, and I know he’s using Excel 2007. Ah.

It never occurred to me that AppleScript would be an option. Truth be told, I’d much rather be doing this myself, in a Mac environment, since having to rely on a third party for such a critical part of the website is risky, even in the best of scenarios. I’ll definitely contact the two gentlemen you suggested (thank you, again!), but am wondering....

If I wanted to move this whole thing to an Apple friendly option, knowing that I’m NOT a programmer or someone who otherwise goes near a spreadsheet, how would one find a local provider who could write the code for iWork’s Pages or similar, then teach me how to do this? What do you look up in the yellow pages or online for something like that?

There are about ten thousand things I can figure out then do really well on my own. This ain’t one of ‘em.

 

[exegete77]

I think that if you want to use iWork, then it has to be iWork 09 (iWork 08 does not support AppleScript).

A lot depends on exactly what you are trying to do. If you are trying to copy what the Windows team does, then there may be a different approach. Without knowing more, it is difficult to recommend anything else. Sometimes (most of the time) database interface with internet works better than spreadsheets. In that case, it might be worth checking some of the database solutions.

 

[exegete77]

While he’s been calling them macros, it does appear he’s actually referring to VBA, and I know he’s using Excel 2007. Ah.

To be more accurate, macros are self-contained procedures in which the statements are written using VBA (Visual Basic for Applications). VBA was officially dropped from MS support in July 2007 for new customers. So while it is still possible to run some of the VBA in Office 2007 it is only a temporary fix. MS moved from VB6 to VB.NET. BTW, this has significant implications for AutoDesk (w/AutoCAD) and Adobe since both are deeply tied into VBA.

So, now the question when this guy supporting you talks about macros, is he referring to using VBA or VB.NET? My guess is VBA, which is limited and does not have a future even on the Windows side.

Sorry for the diversion.

 

[PaperQueen]

So, now the question when this guy supporting you talks about macros, is he referring to using VBA or VB.NET? My guess is VBA, which is limited and does not have a future even on the Windows side.

Sorry for the diversion.

He’s definitely talking about VBA (well, that, and trying really hard to get me to move to PC, which I’m fighting tooth and nail-—especially if VBA has a short future ahead).

So...reading your earlier post...

Here’s the current process:

Stage one:
Supporters register to have their businesses listed on our website. They select one of three forms, based on their business type. I use Google Apps forms to capture the info, which puts them on three matching Google spreadsheets.

Stage two:
Weekly, I download the three spreadsheets in OpenOffice form (.ods).

The three .ods spreadsheets are copied/pasted into an .xls spreadsheet Gregg provided that has VBA capability, which is why I’m using OpenOffice-—VBA works there. The macros copy the entries to one page, adjusting/matching columns as necessary. I then manually correct typos, delete duplicates, recategorize entries that came in on the wrong form, etc. before handing the file off to Gregg.

Stage 3:
Gregg copies that new, combined sheet into the master spreadsheet (currently over 12,000 entries). As I understand it, this is where the five level sort and additional macros come into play. Somehow (magic), that spreadsheet transforms into two columns of business names on my web pages, noted in the original post.


If there’s a smoother, simpler way to do this, I’m ALL FOR IT. Am totally open to using a database solution, but again, am out of my depth.

I know if I need my teeth cleaned, I look up “dentists” in the yellow pages (online, that is). Who/what does one search for when in desperate need of a local resource who can not only build the necessary “tools,” but then teach me how to use them? Gotta find a guru that’s here in town, if possible.....

BTW: No apology necessary regarding “the diversion.” Given how patient and helpful you’re being, you could recite the entire Gettysburg Address and it wouldn’t be a problem.

Although, as long as this post got, well...you might feel like YOU just sat through it instead.

 

[mysterytramp]

Potential option ...

Numbers will do a five-level sort, you could save the sorted data off in an .xls file.

To do a five-level sort, select the data, then select the "Reorganize" button in the toolbar.

A window appears with a single sort option (sort column 1, ascending or descending). Click the "+" to add more sorts.

mt

 

[PaperQueen]

Potential option ...

Numbers will do a five-level sort, you could save the sorted data off in an .xls file.

Hmm...so if Numbers does a five level sort...and if I could find someone to write the necessary Apple Script (guessing that would replace the VBA)...can that be used to convert to the type of webpage listings we use online (see link to example in the original post)?

 

[mysterytramp]

I can envision four levels -- independent/supporter, state, city, name of business -- not sure where the other one comes in (and since I don't I'm worried I'm missing something).

But Numbers is scriptable and you should be able to do what you want.

You could zip some raw data and attach it to a post on this thread and we'd probably be able to get you what you want.

mt

P.S. A database like Bento might do the job in fewer steps, but Numbers should do the trick.

 

[exegete77]

Thanks for stepping in, MysteryTramp. I began a 24 day trip today, so not much time for further help.

Wish you well, PaperQueen.

 

[PaperQueen]

Sorry to have disappeared on you there. Was back on the road for work.

In the course of conversation with a couple of folks, another option has been suggested-—use Automator to create the necessary work flow for Numbers, then use a third party solution (Webmerge) to convert the data to columns of participant names on the website.

Does that sound realistic? You folks are far wiser and more experienced than I on this stuff.....

Thanks again for all the wonderful, giving support. You rock!