Dynamic Filter

[RAMU]

Hi,
I have an excel file where Sheet1 & Sheet2 carrying Data.
In Sheet one is a table (Table1) mentioned below:

item Code	Closing qty
apple	45​
ginger	25​
guava	65​
mango	30​
potato	50​

In sheet2 a bunch of Data are in A1:C9

item Code	warehouse	qty
guava	1​	20​
mango	2​	30​
potato	1​	50​
ginger	2​	25​
guava	1​	25​
apple	3​	20​
apple	1​	25​
guava	3​	20​

I need a dynamic filter formula in F1 (Sheet2), when I shall filter the Table1 (Sheet1) Item Code Column, as an example if I filter it as guava, in the F1 of Sheet 2 will give me result like this:

item Code	warehouse	qty
guava	1​	45​
guava	3​	20​

Pls help
Thanks in advance
Regards
RAMU

 

[hagia_sofia]

Indeed, it should change to the desired result once you filter an item code within Sheet1 - at least that was my understanding of how you would like the formula to work (still it would be easier to create a drop-down list).

 

[RAMU]

Indeed, it should change to the desired result once you filter an item code within Sheet1 - at least that was my understanding of how you would like the formula to work (still it would be easier to create a drop-down list).

sharing the link of the file, in sheet2 yellow highlighted is my desired result since I filtered guava in table in sheet1, it may help you.

Dropbox

www.dropbox.com

 

[RAMU]

sharing the link of the file, in sheet2 yellow highlighted is my desired result since I filtered guava in table in sheet1, it may help you.

Dropbox

www.dropbox.com

Similarly if I select apple & guava both my desired result should be like this in sheet2

Regards
RAMU

 

[hagia_sofia]

Many thanks for sharing, please test the following:

=LET(
u,UNIQUE(A2:B9),
a,UNIQUE(A2:A9&B2:B9),
b,DROP(REDUCE("",a,LAMBDA(x,y,VSTACK(x,SUM(FILTER(C2:C9,A2:A9&B2:B9=y))))),1),
c,IF(BYROW(Table2[item Code],LAMBDA(x,SUBTOTAL(3,x))),Table2[item Code]),
d,XLOOKUP(CHOOSECOLS(u,1),c,c),
e,HSTACK(u,b),
FILTER(e,NOT(ISNA(d))))

 

[RAMU]

When I am pasting your formula in F2 then this error is coming

 

[hagia_sofia]

The #SPILL error means that there are data that prevent the formula from spilling, i.e. delete the data in F2:H5 (only the formula should be there).

 

[RAMU]

The #SPILL error means that there are data that prevent the formula from spilling, i.e. delete the data in F2:H5 (only the formula should be there).

Lovely, very nice, i am amazed on your work, thanks a lot Boss, just one help if I add a column in Table & sheet2 data also named as "Item name" adjacent to the item code columns, what should be the formula. Pls help

 

[hagia_sofia]

Thanks for the feedback. As for the question it depends: if it is true that every unique item code has a unique item name then I think you could just insert the column and fill it with names and it should work without adjusting the formula.

 

[RAMU]

Thanks for the feedback. As for the question it depends: if it is true that every unique item code has a unique item name then I think you could just insert the column and fill it with names and it should work without adjusting the formula.

Yes sir unique name of every unique item code