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.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Customer	Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7	Product 8	Product 9	Product 10	Product 11	Product 12	Product 13	Product 14
2	Customer 1				3
3	Customer 2	2
4	Customer 3			5			6
5	Customer 4
6	Customer 5				8
7	Customer 6
8	Customer 7
9	Customer 8
10	Customer 9
11	Customer 10
12	Customer 11
13	Customer 12
14	Customer 13
15	Customer 14
16	Customer 15
17
18
19	Customer	Product 1	Product 3	Product 4	Product 6
20	Customer 1	0	0	3	0
21	Customer 2	2	0	0	0
22	Customer 3	0	5	0	6
23	Customer 4	0	0	0	0
24	Customer 5	0	0	8	0
25	Customer 6	0	0	0	0
26	Customer 7	0	0	0	0
27	Customer 8	0	0	0	0
28	Customer 9	0	0	0	0
29	Customer 10	0	0	0	0
30	Customer 11	0	0	0	0
31	Customer 12	0	0	0	0
32	Customer 13	0	0	0	0
33	Customer 14	0	0	0	0
34	Customer 15	0	0	0	0
35	sum	2	5	11	6
Sheet5

Cell Formulas
Range	Formula
A19:E35	A19	=LET(s,BYCOL(B2:BS16,LAMBDA(bc,SUM(bc))),VSTACK(HSTACK(A1:A16,FILTER(B1:BS16,s)),HSTACK("sum",FILTER(s,s))))
Dynamic array formulas.

 

[wigarth]

=LET(s,BYCOL(B2:BS16,LAMBDA(bc,SUM(bc))),VSTACK(HSTACK(A1:A16,FILTER(B1:BS16,s)),HSTACK("sum",FILTER(s,s))))

Hi!

Thanks for replying.

I get what you are trying to do, and i have a feeling you are on to something.
I just don't have a clue how to do it myself.
However, I get a "Not valid function" message to this. (i ctrl+shift ckiked enter when pasting this formula into A19
(Excel highlights the "Filter" part of the formula.)

Anything I am not getting?

 

[Fluff]

Are you using 365 as per your profile?

 

[wigarth]

Are you using 365 as per your profile?

Yes I am

 

[wigarth]

Are you using 365 as per your profile?

Yes I am, any chance i could have a look at your actual file?

 

[Fluff]

In that case what build version are you on

 

[wigarth]

In that case what build version are you on

View attachment 102607

 

[wigarth]

View attachment 102608

I got this formula to accept all functions, but it gives me a #name fault instead of the desired results.

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

From your formula, I changed " , " to " ; " and "Filter" to "Filtrer" wich is the norwegian version of the function.

 

[Fluff]

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))))