Forum Discussion

MichelleO's avatar
MichelleO
Copper Contributor
Mar 30, 2022
Solved

Create two lists

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

excel
Formulas and Functions
Macros and VBA
office 365

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Mar 31, 2022

    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

     

    in G5:

     

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

     

     

    • MichelleO's avatar
      MichelleO
      Copper Contributor
      Mar 31, 2022

      Hi! 

       

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

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Mar 31, 2022
        Glad I could help & Thanks for providing feedback
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Mar 30, 2022

    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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 30, 2022

    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.

    • MichelleO's avatar
      MichelleO
      Copper Contributor
      Mar 30, 2022
      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.
      • Starrysky1988's avatar
        Starrysky1988
        Iron Contributor
        Mar 30, 2022
        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.

Resources