Easy for some, mindblowing for me...

[wigarth]

Hi!

My list of products to customers becomes to big to be effective sometimes.

Issue: I Have an area with data i need filter a bit and paste into a new area.

- Original area must not be changed, altered or hidden at any point.
- structure of new area must have structural integrity due to need of linking other sheets to data from it.
- Basically hide all products that does not need to be in the list (0 sold items)

have made an example of my sheet.
Would really apreciate any help.

 

[wigarth]

OK, you will need to translate all the functions, not sure if this is correct but try

=LET(s;BYCOL(B2:BS16;LAMBDA(bc;SUMMER(bc)));VSTACK(HSTACK(A1:A16;FILTRER(B1:BS16;s));HSTACK("sum";FILTRER(s;s))))

Well. some of the translations are a nightmare. There are several sources claiming that an english formula should self adapt, but aparently not. I had some issues finding the right translations.

So I made a macro to write your formula into cell a19, And VBA is all out english (Thank God!)
so then the macro wrote the formula, and translated it for me.
(Worth to know for future non-English issues)

Big thanks to you Fluff for providing a good working solution.

 

[Fluff]

Glad it's sorted & thanks for the feedback.

 

[wigarth]

And of course.... some colleagues use office 2019 for some reason. and then it fails...
Is there a similar way to implement this? i tried asking chatgpt this... So much nonsense came out.

 

[Fluff]

Unfortunately I have no idea how to do that in 2019.