Sort Grouped Rows

tman93hokie

I am working on a sheet that lists our organizations donors for 2024. Each row is a specific donation that include the name, amount, date, and membership number. Some donors give multiple times. I want to be able to summarize/group those folks so that I can create a list of donors for the year with their YTD totals (preferable sorted). Tried to create a report and group by membership number, but then I cannot sort to groups. Any ideas on a better way to do this.

DavidMMM

If you are tracking individual donations, and the name of the donor is consistent if donated multiple times, you should be able to pull your sheet into a report that groups them together for you.

I recommend you create a new report, use your donor sheet as the source sheet, group by Name (or Primary if they are in the Primary column), then Summarize by Sum of Amount - if you have all those columns. You can add a secondary grouping of Year if you have a column that defines that.

Can you share (a redacted/dummy version of) what your donor sheet looks like, maybe your column types are not formatted in a way that a report would want?

Ideally you'd want for columns at a minimum:

Primary = Name / Membership Number

Date Donated

Year of Donation (function based on the Date Donated column)

Amount Donated

And that should flow nicely into a report without much hassle.

tman93hokie

Thanks. Here are the data in my rows:

Date of Donation, Amount, Member Number, Full Name, Donation year, plus some accounting cost codes, as well as First name and last name done separately from full name

I want to be able to summarize and sort by YTD total for each person. In other words, run the report and then be able to rank the groups from most to least in terms of total amont YTD.

DavidMMM

@tman93hokie

You could set up your sheet like so:

• Member Number is the primary column
• DonationYear is a column formula: =YEAR(Date@row)
• Date is a column of column type Date
• Amount is formatted as Currency
• Full Name is a column formula: =Firstname@row+" "+Lastname@row

I recommend, also, that you have another sheet that is a register of the member number versus the names, and you pull the name into the Donations sheet with a column formula INDEX/MATCH or a VLOOKUP, based on the member number - to save you from typing over and over again.

Do you need assistance with that ^ piece as well?

And then creating a report,

You can see I grouped by Year, then by Primary (member number), then added two Summaries: to sum the amount donated per person, and to count the number of donations they made per year.

There isn't a way to sort a grouping, though, so if you have it grouped by Primary, you can't sort the larger groupings any further. The Sort button only works for sorting the rows INSIDE the groupings.

tman93hokie

Thanks so much - you answered what I was really getting at - sorting the grouping. That said, this is really helpful, and I am appreciative of your taking the time to share it.

Daniel Kim

@tman93hokie

Grouping auto-sorts alphabetically. I have a somewhat complex workaround…

You would need to have a separate "reference sheet" that contains your donor reference numbers. I would have another column that uses a SUMIF() of the donors and tie it back to your donations sheet. Use another column with the RANK() Function to return a number, then a helper column that joins the rank with your donor.

On your donations sheet, use a VLOOKUP or index match to return the column with the number and donor, then group your report by the helper.

Below is an example of how I've accomplished this in a RAIDA log

Reference Sheet

RAIDA Log

Report 1

Report 2