excel formula to remove duplicates and sum adjacent values

Is this possible in power query? Is it feasible considering that I'm relatively comfortable doing a decent amount of data manipulation by clicking around, but I've never written M before?ĮDIT: Actually looking through the data more, there's a column that shows the most recent date. I need to pull a deduped list of donors with just their latest gift.

So I have a table full of gifts to our organization.

Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors DateĪ community since MaDownload the official /r/Excel Add-in to convert Excel cells into a table that can be posted using reddit's markdown.ĮDIT: since I realized I had the column I needed, I just used power query to remove the duplicates and extraneous columns, and used SUMIFS to pull the latest gift amount based on the account name matching and the gift date matching the latest gift date. Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data. NOTE: For VBA, you can select code in your VBA window, press Tab, then copy and paste that into your post or comment. To keep Reddit from mangling your formulas and other code, display it using inline-code or put it in a code-block This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified

Only text posts are accepted you can have images in Text posts.

Use the appropriate flair for non-questions.

Post titles must be specific to your problem.

If using dates you will need to format the result cell as a date field or Excel will display the numeric value for the date instead. You can then fill down appropriately to populate the rest of your values. You can tell an Array Function by the curly brackets around the entire request (including equals) To create this as an Array Function press Ctrl + Shift + Enter rather than just Enter You can combing MIN / MAX and IF to lookup the value from an array and then grab the relevant version of the data.īelow I use MAX to find the latest date for each of the unique values. The work around is to use an Excel Array Formula. This only removes unique duplicates, if you start including your date or sales values in the list you find Excel sees only unique values. Later versions of Excel feature a Remove Duplicates button under the Data tab in the ribbon. In Excel sometimes you will need to find either the first or last value from a mixed list of values that also include duplicates.

Excel formula to remove duplicates and sum adjacent values