Google-sheets – Consolidate List of Similar Data into Single Row in Google Spreadsheets

google sheets

I have a Google Spreadsheet that lists donations given by constituents with a unique account number. Several constituents have made more than one donation in a given period of time listed separately. I would like to filter the list of constituents and include each of their gifts in the same row as their account information.

In the attached spreadsheet, Sheet1 shows a sample of the data, and the second sheet shows the desired results as I would like them displayed. I imagine some sort of array formula filter could accomplish this, but I'm at a loss how to construct it. Any help would be appreciated.

I created an example file with expected outcome: Consolidate List of Similar Data into Single Row

Best Answer

If prepared to add a helper column in Sheet1 (say E, with =countif(B$2:B2,B2) in E2 copied down to suit), in C2 of Desired Results please try: 

=sumifs(Sheet1!$D:$D,Sheet1!$B:$B,$B2,Sheet1!$E:$E,right(C$1,1))

and copy down and across to suit.

Alternatively, add a label (say Gift#) in Sheet1 E1 (with helper column as above) and create a pivot table with Account# and Name for Rows (uncheck Show totals), Gift# for Columns and Gift in Values.

Related Topic