Mar 29 2022 06:23 PM
Hello,
I am trying to figure out an automatic formula in excel to replace a pivot table.
I have a table/array of data that includes multitude of information. From that raw data, I have a drop where someone can select criteria. In that I am trying to create a function that first pulls in two lists:
The first list would be dependent on that criteria, than based on that first criteria automatically create a second list. So for example:
In the Data validation Filter a person selects "Fruits"
A formula would know that there are various fruits from the data: Apple, Strawberry, Kiwi, Orange. The formula would first pull in "Apple" then it would know that Mary, John, Harry, and Eliza all purchased Apples so would list all the people who bought Apples. Next to each persons name would be how much money they each spent on Apples (I already i'd figure i'd do this with an if/array formula)
Then the formula would go to the next fruit: "Strawberry" and list the people who purchased Strawberries, then move onto Kiwi.
Do you know how how I'd go about creating this type of formula? Normally I'd do a Sort(filter, but that can't have multiple lists inside it.
Thanks,
Michelle
Mar 29 2022 10:08 PM - edited Mar 29 2022 10:09 PM
@MichelleO Wondering why you would NOT want to use a pivot table for this. Probably doable but why reinvent the wheel? What Excel version are you using, by the way? And perhaps you can share a link to a file that contains a realistic example of what you raw data looks like.
Mar 30 2022 07:09 AM
Mar 30 2022 07:53 AM
Mar 30 2022 08:08 AM
Hi @MichelleO
Similar to a Pivot Table, with LAMBDA & Co. functions
- Drop down list in F4
- In F5:
=SORT(UNIQUE(FILTER(Table1[Kind], Table1[Product]=F4)))
- In G4:
=TRANSPOSE(SORT(UNIQUE(Table1[Name])))
- in G5:
=MAKEARRAY(ROWS(F5#),COLUMNS(G4#),
LAMBDA(rw,cl,
SUM(
FILTER(Table1[Qty],
(Table1[Kind]=INDEX(F5#,rw))
*(Table1[Name]=INDEX(G4#,,cl)),
0
)
)
)
)
Corresponding sample attached
Mar 31 2022 01:25 AM - edited Mar 31 2022 01:26 AM
SolutionHi @MichelleO
Not sure why I didn't think about this easier option first. A simple SUMIFS does it instead of the MAKEARRAY option I posted yesterday. With the same setup
in G5:
=SUMIFS(Table1[Qty], Table1[Kind],F5#, Table1[Name],G4#)
Mar 31 2022 06:43 AM
Hi!
Thank you so much for your trouble shooting/help with the formulas. I sincerely appreciate it!
Mar 31 2022 01:25 AM - edited Mar 31 2022 01:26 AM
SolutionHi @MichelleO
Not sure why I didn't think about this easier option first. A simple SUMIFS does it instead of the MAKEARRAY option I posted yesterday. With the same setup
in G5:
=SUMIFS(Table1[Qty], Table1[Kind],F5#, Table1[Name],G4#)