Duplicate Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1928844%22%20slang%3D%22en-US%22%3EDuplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1928844%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I'm%20new%20to%20using%20Excel%20and%20I've%20been%20trying%20to%20figure%20something%20out.%20%3CSPAN%3EI'm%20doing%20an%20Excel%20sheet%20of%20people%20who%20donated%20to%20a%20fund%20with%20their%20names%20and%20the%20amount%20they%20donated.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMy%20issue%20is%20there%20are%20about%205%2C000%20rows%20of%20people%20and%20most%20of%20these%20are%20duplicates%20names.%20(For%20example%2C%20if%20their%20name%20is%20John%20Smith%20their%20name%20will%20come%20up%20multiple%20times%20if%20they%20donated%20multiple%20times)%20Their%20names%20are%20column%20%22A%22%20and%20the%20amount%20they%20donated%20is%20column%20B.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20donation%20totals%20are%20supposed%20to%20be%20aggregated%20(that%20if%20someone%20made%20separate%20donations%20I%20add%20them%20together%20and%20just%20have%20one%20line%20per%20individual-%20with%20their%20total)%20Is%20there%3C%2FSPAN%3Ean%20easier%20way%20to%20do%20this%20without%20having%20to%20manually%20add%20all%20the%20donations%3F%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20trying%20to%20add%20up%20John%20Smith's%20for%20example%20donation%20totals%20without%20having%20to%20do%20it%20all%20by%20hand.%20I%20didn't%20know%20if%20Excel%20was%20able%20to%20add%20these%20totals%20together%20for%20individual%20people.%20The%20screenshot%20isn't%20the%20actual%20Excel%20sheet%20I'm%20doing%2C%20just%20an%20example%20I%20made%20up.%20Thanks%20for%20any%20help.%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-11-23%20at%205.10.36%20PM.png%22%20style%3D%22width%3A%20378px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235845i10DFF9AAEB7DC48E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-11-23%20at%205.10.36%20PM.png%22%20alt%3D%22Screen%20Shot%202020-11-23%20at%205.10.36%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1928844%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1929483%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1929483%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F880672%22%20target%3D%22_blank%22%3E%40RileyTim184%3C%2FA%3E%26nbsp%3BThe%20easiest%20way%20out%20would%20be%20to%20use%20a%20Pivot%20Table.%20See%20the%20attached%20workbook%20for%20a%20working%20example.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-11-25%20at%2007.35.46.png%22%20style%3D%22width%3A%20621px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F235925iFFC9774D6AA137E3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-25%20at%2007.35.46.png%22%20alt%3D%22Screenshot%202020-11-25%20at%2007.35.46.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESearch%20the%20web%20for%20many%20resources%20to%20learn%20more%20about%20Pivot%20Tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1929547%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1929547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F880672%22%20target%3D%22_blank%22%3E%40RileyTim184%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20using%20Excel%20365%2C%20there%20are%20some%20newish%20worksheet%20functions%20that%20will%20also%20do%20the%20job.%26nbsp%3B%20Given%20the%20two%20columns%20'name'%20and%20'donation'%2C%20a%20list%20of%20distinct%20names%20is%20given%20by%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20UNIQUE(name)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20the%20donations%20for%20each%20distinct%20name%20are%20given%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SUMIFS(donations%2C%20name%2C%20distinct%23)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%5BNote%3CEM%3E%3A%20If%20the%20name%20'distinct'%20is%20applied%20to%20the%20first%20cell%20containing%20the%20formula%2C%20'distinct%23'%20will%20be%20its%20spilt%20range%3C%2FEM%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1932348%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932348%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BOh%20great%20thank%20you!%20I%20didn't%20know%20I%20could%20do%20this.%20Very%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1932351%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932351%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20help!%20I%20will%20try%20this%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1932608%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1932608%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI'm%20trying%20to%20figure%20out%20the%20Pivot%20table%20and%20I'm%20having%20a%20little%20trouble.%3C%2FP%3E%3CP%3EThe%20'Donations!%24A%241%3AD%20.....%22%20screenshot%20is%20what%20pops%20up%20first%20when%20I%20hit%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-11-25%20at%205.57.58%20PM.png%22%20style%3D%22width%3A%20493px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236191iF6797F4F5A164108%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-11-25%20at%205.57.58%20PM.png%22%20alt%3D%22Screen%20Shot%202020-11-25%20at%205.57.58%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThere's%20an%20A%2C%20B%2C%20C%2C%20and%20D%20column%20for%20their%20name%2C%20address%2C%20town%2C%20and%20how%20much%20they%20donated.%20A%20is%20the%20name%20column%20and%20B%20is%20the%20donations%20like%20I%20said%20earlier.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-11-25%20at%205.53.42%20PM.png%22%20style%3D%22width%3A%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236192i8E5433BF9680A8E1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-11-25%20at%205.53.42%20PM.png%22%20alt%3D%22Screen%20Shot%202020-11-25%20at%205.53.42%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20hit%20the%20%22Ok%22%20button%20and%20I%20checked%20the%20name%20and%20aggregated%20ytd%20(aka%20donations)%20boxes.%3C%2FP%3E%3CP%3EThe%20issue%20I'm%20having%20is%20I%20think%20it's%20adding%20the%20columns%20altogether%20(added%20the%20screenshot%20to%20show)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202020-11-25%20at%205.52.07%20PM.png%22%20style%3D%22width%3A%20542px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236193i8D98D6FB30CE2A47%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202020-11-25%20at%205.52.07%20PM.png%22%20alt%3D%22Screen%20Shot%202020-11-25%20at%205.52.07%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAm%20I%20missing%20a%20step%3F%20Do%20I%20have%20to%20highlight%20a%20certain%20section%20before%20hitting%20the%20pivot%20table%20button%20or%20do%20I%20have%20to%20put%20in%20something%20different%20under%20the%20%22Select%20a%20table%20or%20range%22%20part%20at%20the%20%22Create%20PivotTable%20box%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

So, I'm new to using Excel and I've been trying to figure something out. I'm doing an Excel sheet of people who donated to a fund with their names and the amount they donated.

My issue is there are about 5,000 rows of people and most of these are duplicates names. (For example, if their name is John Smith their name will come up multiple times if they donated multiple times) Their names are column "A" and the amount they donated is column B.

The donation totals are supposed to be aggregated (that if someone made separate donations I add them together and just have one line per individual- with their total) Is therean easier way to do this without having to manually add all the donations?

I'm trying to add up John Smith's for example donation totals without having to do it all by hand. I didn't know if Excel was able to add these totals together for individual people. The screenshot isn't the actual Excel sheet I'm doing, just an example I made up. Thanks for any help.Screen Shot 2020-11-23 at 5.10.36 PM.png

7 Replies

@RileyTim184 The easiest way out would be to use a Pivot Table. See the attached workbook for a working example.

Screenshot 2020-11-25 at 07.35.46.png

 

Search the web for many resources to learn more about Pivot Tables.

@RileyTim184 

If you are using Excel 365, there are some newish worksheet functions that will also do the job.  Given the two columns 'name' and 'donation', a list of distinct names is given by the formula

= UNIQUE(name)

and then the donations for each distinct name are given by

= SUMIFS(donations, name, distinct#)

[Note: If the name 'distinct' is applied to the first cell containing the formula, 'distinct#' will be its spilt range)

@Riny_van_Eekelen Oh great thank you! I didn't know I could do this. Very helpful.

Thank you for your help! I will try this out.

 

@Riny_van_Eekelen 

I'm trying to figure out the Pivot table and I'm having a little trouble.

The 'Donations!$A$1:D ....." screenshot is what pops up first when I hit the pivot table.

Screen Shot 2020-11-25 at 5.57.58 PM.png

There's an A, B, C, and D column for their name, address, town, and how much they donated. A is the name column and B is the donations like I said earlier. 

Screen Shot 2020-11-25 at 5.53.42 PM.png

I hit the "Ok" button and I checked the name and aggregated ytd (aka donations) boxes.

The issue I'm having is I think it's adding the columns altogether (added the screenshot to show)

 

Screen Shot 2020-11-25 at 5.52.07 PM.png

Am I missing a step? Do I have to highlight a certain section before hitting the pivot table button or do I have to put in something different under the "Select a table or range" part at the "Create PivotTable box? Thanks.

@RileyTim184 From your screenshots I can see you're on a Mac. So, am I. That makes it easier to explain. When you created the table, you dragged both Name and Donation into the "Value field". Drag the Name into the "Rows" field (red arrow). 

Screenshot 2020-11-26 at 05.29.10.png

Now press the (i) to the right of where it says "Count of Amount" (blue arrow). It should look like this;

Screenshot 2020-11-26 at 05.35.16.png

You'll ses the "Count" is highlighted in the "Summarise by" box. Select "Sum" to tell Excel you want to add up the amounts, not count them. The end result will look like this (use the file I uploaded with my initial response as a reference):

Screenshot 2020-11-26 at 05.26.27.png

Now, if you never got this far and Excel defaulted to count amounts, it means that your list of donations contains texts. They may look like numbers, but they are not. Then you need to fix that first. To see if your amounts are (or contain) texts, put the formula =ISNUMBER(D2) in E2 and copy it all the way down. If anywhere it returns FALSE you'll find your text. Let me know your findings.

It worked! Thank you so much, I really appreciate it. You just saved me so much time, thanks again.