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 dro...
excel
Formulas and Functions
Macros and VBA
office 365
  • Lorenzo's avatar
    Lorenzo
    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#)

     

     

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

Resources