Ugh. I despise pivot tables with ID and Name in Rows area. I am acknowledging it is method #4, but not one I am going to pursue. Way back in 2010, the PowerPovot add-in offered a glorious “Flat Pivot Table” option as the 8th item in the drop-down. That is a button that should have been kept! But the Excel team ripped it out (probably the same people who thought Compact Form should be the default Layout!) If you forced me to try it and count the keystrokes to turn off the subtotals, even if it is shorter than method 1, I still won’t go down that path.
OK, I didn't realize it was that sensitive a subject... ;-) For me the simple adage is this: we have data tables (could be structured into data models): for those we use Excel tables (O-Oh), and we have reports, and for that we use pivot tables. But data tables and reports should not mix: Excel doesn't handle those combos all that well, and many simple features like charts and all stop working on those. OTOH, we could make the desired table in a single cell "table formula", with all the trimmings needed. As a matter of fact, I'm sure ExceLambda has already finished the LAMBDA for exactly that in the time I wrote this comment. LOL! 🙂
Why not use dynamic arrays. With ID and Customer next to each other A1 = Unique(Table1[[ID]:[Customer]]) to get the unique combinations of ID and Customer. Then in C1 = =SUMIFS(Table1[Quantity],Table1[ID],INDEX(A1#,,1),Table1[Customer],INDEX(A1#,,2)). Do this SUMIFS for each column you want summed. No PQ. Quick and simple and always up to date based on data.
Method number six! I used to say there were five ways to do anything in Excel. Today, with the addition of Power Query, DA, Lambda, I bet we are up to ten ways.
Hey what u did in excel you didnot merge back in power query. If you can merge back tge calculation in powerquery and make it insert like in you did in subtotals i would be grateful.. thanks in advance
Goodly happened to post a video with a function to determine sub totals two days ago here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YccjLy-t44g.html
Kara’s original email is in the video description. She doesn’t want the detail rows and the totals, just the totals. And, she is already happy to use the method in the video. And, it is Friday and I am already out of the office so I don’t have access to the fake data I created. Whatever fake data we use doesn’t match Kara’s data, so coding something in M won’t translate for Kara.
I have a question and I'm not sure how to search for it so I'm hoping you can help. I have two sets of numbers that I need to match up to compare. The problem is, some of them need to be added together first but there's no consistency in which numbers should be added together. Is there a way to automate checking if essentially randomized sums within a data set match numbers within another data set? Idk if I'm explaining this well but for example, sometimes 4 rows in one column equal 1 row in another column. Sometimes it's 6 rows in one column to match 1 in another. Sometimes it's 1:1. There's no pattern. Can it still be automated?
Your problem sounds like an old mathematical puzzle named the Knapsack Problem. I frequently get the question from someone who works in Accounts Receivable. They say, "I received a check for $12,345.67 from a vendor. The vendor has 72 open invoices. Which of those 72 open invoices add up to $12,345.67." A very long time ago, back in 2002, I had problem like this on my website. Over 3000 people weighed in with solutions, but the winning solution was some VBA code by IOANNIS. You can see that VBA code here: www.mrexcel.com/challenges/accounts-receivable-challenge/ For smaller data sets, I have a formula solution that tests every possible combination, but due to current limits in Excel, it can only test a population of 19 open invoices. If someone had 72 open invoices for one customer, then it would require trillions of Excel worksheets. It is not scalable. If I had to do this, I would try to adapt IOANNIS's VBA code to solve one check at a time. But you should also google "Solve Knapsack problem in Excel" to see if anyone else has come up with something better.
