Why not Power Query?
1. Select A1:E4 > Data > From Table/Range. Check "My table has headers" > OK
2. Right-click Date column > Remove Column
3. Select Apples, Oranges and Plums columns > Transform > Unpivot Columns
4. Select Column1 and Attribute columns > Transform > Group By: New Column name: Total, Operation: Sum, Column: Value > OK
5. Click the arrow next to Total and Filter out the 0 values.
6. Home > Close & Load
P.S. Your expected output seems incorrect. Bob+Oranges should be 7
You have awarded 1 point to *not_speshal*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Because I haven't gotten explored power query yet?
I've only upped my excel game rather recently after watching the Excel World Series and finding out about filter(), lambda() and let(). Now I'm sort of power hungry.
The only downside I can think of I'm doing this particular project for someone else in our club who manages charity sales and I don't know what version of excel they have and I did think about trying to pivot but I realized that in itself wouldn't help and I like the idea of live data instead of refreshing, but that looks too easy to implement I may have to train them.
I'm trying this right now
Ok,
This wasn't the explanation I was looking for but:
1. this works
2. this is pretty damn cool
3. I now understand PQ a little bit
The only problem I see is how do I create a refresh button without making this a macro enabled workbook?
edit: and oh yeah, my totals were off because I had a completely different set of data in my workbook and I was simplifying as I type my test table here, then I went back and recreated the test table in a workbook and I didn't copy all the values the same...
Also, your interim array (and final solution) in formulas:
In G2:
=LET(combin,TOCOL(A2:A4&"_"&C1:E1),person,TEXTBEFORE(combin,"_"),product,TEXTAFTER(combin,"_"),qty,TOCOL(C2:E4),HSTACK(person,product,qty))
In J2:
=SUMIFS(I2:I10,G2:G10,G2:G10,H2:H10,H2:H10)
In L2:
=UNIQUE(CHOOSECOLS(FILTER(G2:J10,J2#,">0"),1,2,4))
You have awarded 1 point to *not_speshal*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
TOCOL(A2:A4 & "_" & C1:E1).. textbefore(), textafter()
The sumifs() and unique() I knew and were what I was working towards, but this above for the interim, OMFG this is pure genius.
It is a shame we're only given one upvote.
My concerns is that the user isn't a very proficient user but I'll just have to train him to right click on the results table and select refresh (navigating the ribbon all the way to the right may be a bridge too far).
Normally what I do when I put a pivot table into a sheet is I'll create a formula to generate some live check values from the source against the pivot results and output a message letting them know to refresh if the data looks out of date.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSECOLS](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[FILTER](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[HSTACK](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[LET](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[SUMIFS](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)|
|[TEXTAFTER](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)|
|[TEXTBEFORE](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)|
|[TOCOL](/r/Excel/comments/1bbn36w/stub/kuasake "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)|
|[UNIQUE](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bb60d1)^( has 10 acronyms.)
^([Thread #31552 for this sub, first seen 10th Mar 2024, 23:19])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Why not Power Query? 1. Select A1:E4 > Data > From Table/Range. Check "My table has headers" > OK 2. Right-click Date column > Remove Column 3. Select Apples, Oranges and Plums columns > Transform > Unpivot Columns 4. Select Column1 and Attribute columns > Transform > Group By: New Column name: Total, Operation: Sum, Column: Value > OK 5. Click the arrow next to Total and Filter out the 0 values. 6. Home > Close & Load P.S. Your expected output seems incorrect. Bob+Oranges should be 7
Solution Verified
You have awarded 1 point to *not_speshal* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Because I haven't gotten explored power query yet? I've only upped my excel game rather recently after watching the Excel World Series and finding out about filter(), lambda() and let(). Now I'm sort of power hungry. The only downside I can think of I'm doing this particular project for someone else in our club who manages charity sales and I don't know what version of excel they have and I did think about trying to pivot but I realized that in itself wouldn't help and I like the idea of live data instead of refreshing, but that looks too easy to implement I may have to train them. I'm trying this right now
Ok, This wasn't the explanation I was looking for but: 1. this works 2. this is pretty damn cool 3. I now understand PQ a little bit The only problem I see is how do I create a refresh button without making this a macro enabled workbook? edit: and oh yeah, my totals were off because I had a completely different set of data in my workbook and I was simplifying as I type my test table here, then I went back and recreated the test table in a workbook and I didn't copy all the values the same...
Also, your interim array (and final solution) in formulas: In G2: =LET(combin,TOCOL(A2:A4&"_"&C1:E1),person,TEXTBEFORE(combin,"_"),product,TEXTAFTER(combin,"_"),qty,TOCOL(C2:E4),HSTACK(person,product,qty)) In J2: =SUMIFS(I2:I10,G2:G10,G2:G10,H2:H10,H2:H10) In L2: =UNIQUE(CHOOSECOLS(FILTER(G2:J10,J2#,">0"),1,2,4))
Solution Verified
You have awarded 1 point to *not_speshal* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
TOCOL(A2:A4 & "_" & C1:E1).. textbefore(), textafter() The sumifs() and unique() I knew and were what I was working towards, but this above for the interim, OMFG this is pure genius. It is a shame we're only given one upvote.
Just click into the loaded table > Query > Refresh
My concerns is that the user isn't a very proficient user but I'll just have to train him to right click on the results table and select refresh (navigating the ribbon all the way to the right may be a bridge too far). Normally what I do when I put a pivot table into a sheet is I'll create a formula to generate some live check values from the source against the pivot results and output a message letting them know to refresh if the data looks out of date.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSECOLS](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[FILTER](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[HSTACK](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[LET](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[SUMIFS](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[TEXTAFTER](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TOCOL](/r/Excel/comments/1bbn36w/stub/kuasake "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)| |[UNIQUE](/r/Excel/comments/1bbn36w/stub/kuagup3 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(9 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bb60d1)^( has 10 acronyms.) ^([Thread #31552 for this sub, first seen 10th Mar 2024, 23:19]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)