SOLVED

Create two lists

New Contributor

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

7 Replies

@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.

Hi Riny, Because this is a shared document that a bunch of people go in and change/break the pivot, we're trying to get away from them if possible. I would normally live for Pivots, but trying to think of a new way to use. Raw data is extensive and in a table with multiple columns identifying each piece of this (can't share bc of proprietary info). Using Microsoft 365.
If the file can not be shared due to sensitive or privacy issue, we can only give you advice to use FILTER function as you are using Office 365. To my knowledge,using FILTER function shall be the best.

Hi @MichelleO 

 

Similar to a Pivot Table, with LAMBDA & Co. functions

Screenshot.png

- 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

best response confirmed by Hans Vogelaar (MVP)
Solution

Hi @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

 

Screenshot.png

in G5:

 

=SUMIFS(Table1[Qty], Table1[Kind],F5#, Table1[Name],G4#)

 

 

Hi! 

 

Thank you so much for your trouble shooting/help with the formulas. I sincerely appreciate it! 

Glad I could help & Thanks for providing feedback