Creating a list that combines identical items as one item

Question

Creating a list that combines identical items as one item

I am building a sheet to be used by my church for the counters to use on Sunday to help automate the process. One item to be identified are categories for "Special Giving." I have created a category column. Each cell in this column contains a popup menu of approximately 12 items that can be selected. Further down on the sheet I've created rows using the SUMIF function to total up the cost for each selection. Is there a way to created a column that will only show a selection from the category column one time, ultimately to provide me with a column with each category selected only shown once.

Example: The category column may show three selections for "Altar Flowers" and one selection for "Music" and maybe a third selection for "UMCOR." The column I want to create automatically would contain three items "altar Flowers", "Music", and "UMCOR." I need this to automated the SUMIF functions

MacBook Air 13″, macOS 15.3

Posted on Feb 25, 2025 6:31 PM

Reply

Answer 1

SGIII

Level 8

36,458 points

Feb 25, 2025 10:52 PM in response to Charles Niemi

A screenshot would help to understand exactly what you are doing.

However, from your description it seems likely that using Pivot Tables would save you a lot of work.

Pivot Tables are easy to use, just a few clicks and drags. And using them means you don't have to enter and debug formulas with SUMIF and SUMIFS.

Among other things Pivot Tables will do exactly the kind of "reduplication" that you describe.

This simple example took about a minute.

Click in table with data
Choose Organize > Create Pivot Table > On Current Sheet
Drag fields as shown on right.

Many variations on this are possible with a few clicks. More here:

Intro to pivot tables in Numbers on Mac - Apple Support

SG

Reply

Answer 2

SGIII

Level 8

36,458 points

Feb 26, 2025 6:16 PM in response to Charles Niemi

Charles Niemi wrote:

I've used lookup to copy the results from the pivot table to the summary sheet. The problem is every time the pivot table is updated, it destroys the links in the summary sheet.

There are ways to extract data from Pivot Tables by formula.

GETPIVOTDATA - Apple Support

However, unless the summary needs to be updated every few minutes or so, I've find good old copy-paste (copy, click in existing summary destination table, Edit > Paste and Match Style) does the job well.

The Pivot Table saves a lot of work. The copy-paste only takes a second or so, followed by a little touch-up formatting before distribution to others.

You could also (though it's more work) set up a table with SUMIF or SUMIFS formulas in it and just copy-paste the list of distinct (de-duplicated) categories from the Pivot Table into the appropriate column.

SG

Reply

Answer 3

Feb 26, 2025 4:34 AM in response to SGIII

Thanks. Last night (late) I came across a tutorial describing the use of the pivot table. Exactly as you have shown, with a few alterations of the data input sheet I created, the pivot table was the perfect answer. Eliminated the entire use of SUMIF statements.

Thank you very much

Reply

Answer 4

Feb 26, 2025 4:20 PM in response to SGIII

The pivot table has solved one issue, but created another. My data base consists of three sheets. Master sheet of members - This allows me to create a popup lists to find members and their account numbers. An input sheet where the members are identified, and their contributions recorded. This sheet includes the Category column. The final sheet is a summary sheet where all of the costs are recorded. This is the sheet that is printed and distributed the appropriate members. I've used lookup to copy the results from the pivot table to the summary sheet. The problem is every time the pivot table is updated, it destroys the links in the summary sheet. I have to create this sheet for people that don't even like computers

Reply

Quick Links

Creating a list that combines identical items as one item