Adding blank rows between UNIQUE FILTER list

[milovskilab]

Hi! Been scratching my head with this all afternoon - I want to filter a long list to give me unique values, but I want my new list to have 3 spare rows between each item.

I have used an excellent formula that i found on this forum, but it is consistently ignoring the last item on my filtered list... can anyone help please!

The formula is

=LET(u,SORT(UNIQUE(FILTER(Stk_Trans[[#All],[STOCK_CODE]],Stk_Trans[[#All],[STOCK_CODE]]<>""))),s,SEQUENCE(ROWS(u)*2-1,,,0.75),IF(MOD(s,1)<>0,"",INDEX(u,s)))

And it works like a dream apart from ignoring the last item on the filtered list. I am sure i am missing something really simple, but I just can't see it!

Thanks in advance, you clever people!

 

[Anonymous1378]

Try SEQUENCE((ROWS(u)-1)*4+1,,,0.75) for s?

 

[JamesCanale]

I couldn't quite reproduce what you had.

MrExcelPlayground19.xlsx
	I	J
27	A	A
28	B
29	C
30	D
31	E	B
32	F
33	G
34
35		C
36
37
38
39		D
40
41
42
43		E
44
45
46
47		F
48
49
50
51		G
Sheet1

Cell Formulas
Range	Formula
J27:J51	J27	=LET(u,I27:I33,s,SEQUENCE(ROWS(u)*4-3,,,0.25),IF(MOD(s,1)<>0,"",INDEX(u,s)))
Dynamic array formulas.

 

[milovskilab]

I couldn't quite reproduce what you had.

MrExcelPlayground19.xlsx
	I	J
27	A	A
28	B
29	C
30	D
31	E	B
32	F
33	G
34
35		C
36
37
38
39		D
40
41
42
43		E
44
45
46
47		F
48
49
50
51		G
Sheet1

Cell Formulas
Range	Formula
J27:J51	J27	=LET(u,I27:I33,s,SEQUENCE(ROWS(u)*4-3,,,0.25),IF(MOD(s,1)<>0,"",INDEX(u,s)))
Dynamic array formulas.

Perfect! Thank you - it was the *4-3,,,0.25 that fixed it for me. Thank you so much!

 

[RoryA]

If you have the functions available:

=TOCOL(LET(u,SORT(UNIQUE(FILTER(Stk_Trans[[#All],[STOCK_CODE]],Stk_Trans[[#All],[STOCK_CODE]]<>""))),EXPAND(u,,4,"")))