Triming down data from a 90+ column worksheet

[jockojkj]

I have developed a large web based form that collects data relative to a church’s ministry registration. I have imported the .csv file into an Excel spreadsheet successfully. There are over 90 columns for each record. Many of the columns pertain to a given ministry and is either blank, or has a value of “New”, “Continue” or “Remove”. I was ecstatic to discover that by using a Pivot Table I could collect and display the data by ministry. Using that approach I was able to generate lists for each ministry that I then copied into their own worksheet and labeled accordingly. I then printed them out to separate PDF files which I then routed to the ministry leaders via e-mail for follow-up (saving postage costs!)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
To help with getting an overview of each individual’s sign-up, I used Word’s mail merge function. With insert text before merge field functionality, I was able to place a friendly label before the value. Only non-blank values were displayed. For example, after listing the volunteer’s identifying information, the ministries were listed… “Adult Choir – Continue”, and in some cases additional ministries might be included… “Knights of <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:City w:st="on"><st1lace w:st="on">Columbus</st1lace></st1:City> – New”, etc. I ended up with a 325 page PDF with one page for each volunteer’s information.
<o></o>
I was asked to generate a list that would be slimmer than the .csv sheet which displays the raw data. It should have multiple rows, one for each instance where a volunteer signs up for a ministry, listing the ministry and enrollment status. So the person above (call him Smith, Joe) would have two rows… Smith, Joe / Adult Choir / Continue, and Smith, Joe / Knights of Columbus / New… plus any other data (phone number). This could then be sorted on any of the fields.
<o></o>
The diocese has us use a Parish Data System (PDS), but it is too cumbersome to generate timely lists. So the quick online form allowed for rapid data entry and collection of the data so we could send out the lists quickly. Eventually, PDS will have the data entered and the listing sought might be generated by that (but I am not familiar enough with that to be sure.)
<o></o>
I am beginning to wonder if I might be able to do this in Access or other database application (Alpha 5?) to accomplish the parsing of lists down to one record for each ministry volunteer with some sort of active status listed. Before I wrestle with that, I thought I toss this problem out here for some discussion! Whatever suggestions MrExcel can provide would be fantastic!

 

[BrianB]

Have you tried double-clicking a value in a pivot table to get the records that make it up ?

Could record a macro to do this, and delete the columns you do not want.

I spent 15 years doing monthly reporting, some data with up to a million rows (in Access). Found that pivot tables solved a whole load of problems. Have as many different ones as you need. My "customers" did not want to see them - more interested in the monthly "top level" comparison data analysed into various categories for their area. But then, of course, they wanted to be able to "drill down" to see what made up certain questionable numbers. This makes for very efficient reporting from their point of view. And the big boss. No need to see the detail if subtotals look ok. Time can then be spent on examining the "exceptions" (big variances) in detail.

I would not use Access unless the total number of records is likely to get to the Excel maximum of 65,536.

You data basis seems correct. A single worksheet table with headings in row 1. Unformatted. The best efficiency is achieved by having a "Month" column. Add new data to the bottom. Fill in the "Month" column. Update the pivot table. Have made up reports with columns for 12 months (temporarily hide the empty columns) and Vlookup() into the reports. A whole set of monthly reports for each area in minutes. One reason I have been able to post nearly 8,000 messages here.

Have the whole set in the same workbook with identical "area" worksheets. Same format, different contents. Have a top level sheet that sums all the others. If this does not match the pivot totals then someone has done something and not told you about it. In a big telecomms company, for example, they were always setting up new areas. So I copy an existing area worksheet, change the formulas (or, more specifically, change a single cell in the worksheet to change the formulas) and include that in the set.

Not only did we have lots of "areas" but we also had "regions" containing areas. The region workbooks consisted of the same worksheets with simple links to the top level area workbook sheets. And another for the region summary. The big, big chief got a very slim workbook with just the regional sheets and top level summary for the whole company.

The whole approach is "bottom up".

Hope this helps.