subtotal data and include other info from row

Short version: I'm trying to find out if there's a way, when using the Subtotal button in Excel 2007 and collapsing the data to level 2, to show more than just the two pieces of information it defaults to (the field you pick under "At each change in:" and the field you check under "Add subtotal to").

Long version: I have a monthly project that seems to take more time than I think it should, so I'm trying to find quicker ways to handle it. I have a spreadsheet of people who have donated to our organization and it includes some extra information about them such as their ID. I'd like to include the ID, Last Name and First Name in the Level 2 view (so we can manipulate the data in different ways).

I attached a sample workbook that includes three worksheets that I think are self explanatory.

If anyone has any idea if this is possible and how I can accomplish it, I'd really appreciate the help.

Thanks!

Hi, K m. Thanks for the quick reply.

I'm trying to replicate what you did as if I was starting from scratch but I'm not having any luck and I think I'm a little confused about the formulas you used and how they work. If you wouldn't mind answering a couple more questions, I'd really appreciate it.

When I export this list from my database, it has only one worksheet; it comes out looking like the one called "original data". So would my first step be inserting a new blank worksheet and then entering the formulas you came up with (changing the ranges and name of the worksheet as needed)? The columns for ID and Name on Report don't have a formula so I'm not sure how to get that info on the other worksheet.

Did you use the second worksheet ("after subtotaling") at all or can I just skip that step?

Thanks!

PS: I LOVE the helper column removing the word "total"! I was doing it by using search and replace but unfortunately we actually have a couple of companies with "total" in their name and I'd have to remember that and go back and fix it.

Hi, K m. Thanks for the quick reply.

I'm trying to replicate what you did as if I was starting from scratch but I'm not having any luck. I think I'm a little confused about the formulas you used and how they work. If you wouldn't mind answering a couple more questions, I'd really appreciate it.

When I export this list from my database, it has only one worksheet; it comes out looking like the one called "original data". So would my first step be inserting a new blank worksheet and then entering the formulas you came up with (changing the ranges and name of the worksheet as needed)? The columns for ID and Name on Report don't have a formula so I'm not sure how to get that info on the other worksheet.

Did you use the second worksheet ("after subtotaling") at all or can I just skip that step?

Thanks!

PS: I LOVE the helper column removing the word "total"! I was doing it by using search and replace but unfortunately we actually have a couple of companies with "total" in their name and I'd have to remember that and go back and fix it.

Wow, that was fast! You have no idea how much I appreciate your help, Kirk. These reports are driving me crazy.

I do understand most of the formulas you used, so that's good, but there are a couple of things I'm not clear on. Let me just ask the first one because it could explain everything else.

I recommend that you eliminate adding a TOTAL on the end of the entrys. If you want to say Total add that to the column header

I'm with you on this one. I didn't add that myself, though. Excel does that when it subtotals. But based on your comments, it sounds like I shouldn't bother using the Subtotal button anymore.

Here is the explanation:
- The helper column strips away the "Total" from the end. For example: "The ABC Corporation Total" becomes "The ABC Corporation"

I understand the formula you used in the helper column (F), =LEFT(B2,FIND("Total",B2)-2), but I don't understand how column B got populated in the first place (or column A, for that matter). I don't see anything referencing the "original data" worksheet in column B. Did you just use my data or did you do something tricky that pulled the info from the "original data" worksheet? If you used mine, that's fine, but I used the second spreadsheet ("after subtotalling") to create that list so maybe I do need to keep using the Subtotal button and this report might just be a three-step process.

Thanks again.

That's cool. You did all the hard work for me, maybe I can figure out the frequency function. Okay, probably not, but I bet there's a thread around here somewhere that has some info about it.

Thanks so much for all your help!

Originally Posted by K m

I assume you did not set up the spreadsheet originally. Whoever set it up, did most of the "thinking" for it. For example, they had to decide which numbers to sum. The spreadsheet was not set up to be automated.

Nope, it was me and it was automated. I just used the Subtotal button on the Data tab under Outline, it just didn't give me all the info I wanted.

Originally Posted by K m

A quick review of the web shows you can extract unique names using a PIVOT TABLE

I played around with a pivot table for about 15 minutes but it was creating two rows for each name and we didn't want that. I'll spend some more time with it (and maybe the FREQUENCY function you mentioned) this weekend from home where it's not as loud and busy and I can actually get some thinking done.

Thanks!

This looks really helpful and exactly what I need. I have to learn more about arrays to really understand what you did so I bookmarked that link you provided to check out later.

Thank you so much for your help! I'm really grateful that you managed to take my multi-step method (that wasn't even giving me everything I wanted) and turn it into pretty much a one-step process.

Oops! I gave you reps on one of your other posts in this thread so I can't do it again until I spread some around. But I did mark the thread solved and I'll go back to any old questions I asked and do the same.

Thanks!