SPECIFIC ARRAY COLLATE

[Shakeable_Drip]

Hello,

I have a filter array that I'd like to collate like the below, is this possible? I don't want to collate by UNIQUE, I want to "remove" the next duplicates in the set. Thanks for your time!

data result
2 2
1 1
1 2
1 1
1
1
2
2
1
1

 

[Shakeable_Drip]

Not sure that I got you....

Book1
	A	B	C
1
2	2		2
3	1		1
4	1		3
5	3		1
6	1		2
7	1		1
8	2
9	2
10	1
11	1
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=LET(a,A2:A11,b,OFFSET(a,1,0)<>a,FILTER(a,b))
Dynamic array formulas.

I used a helper column to test the let

=LET(a,Q24#,b,OFFSET(a,1,0)<>a,FILTER(a,b))

after it was successful, I tried to remove the helper and wrap the let around my equation in Q24# and I got "value is of the wrong type"
please forgive me, the below code is super brutforce, I dont know anything about optimizations yet, except not to use full column vlookups

=LET(a,--SUBSTITUTE(TRIM(SUBSTITUTE(FILTER(INDIRECT("txt_data!"&K4&ROW(INDEX(INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),MATCH("     <NestedTube index="&CHAR(34)&S1&CHAR(34)&">",INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),0)))&":"&K4&ROW(INDEX(INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),MATCH("     <NestedTube index="&CHAR(34)&S1&CHAR(34)&">",INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),0)))+(AB2*17)+13),ISNUMBER(SEARCH("        <PartIndex>",INDIRECT("txt_data!"&K4&ROW(INDEX(INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),MATCH("     <NestedTube index="&CHAR(34)&S1&CHAR(34)&">",INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),0)))&":"&K4&ROW(INDEX(INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),MATCH("     <NestedTube index="&CHAR(34)&S1&CHAR(34)&">",INDIRECT("txt_data!"&K4&"1"&":"&K4&$Q4),0)))+(AB2*17)+13)))),"/","")),"<PartIndex>",""),b,OFFSET(a,1,0)<>a,FILTER(a,b))

 

[Shakeable_Drip]

It would have helped if you had given us ALL the details at the start.

The important part was working with the spill right? that's what I should have said not "filter array"

 

[Shakeable_Drip]

I'm guessing I'm using the offset improperly. I've narrowed down "value is of the wrong type" to there and I'm not sure what I'm missing. I think I've used offset to offset an array before, the resulting spill array *can* be offset. I'll re-write the formula using defined names so its easier to understand.

 

[Shakeable_Drip]

I changed the order in which I was doing things and added a helper column.

=INDEX(I3#,MATCH(NTINDEX_SUB,H3#,0))

fills my helper column and

=FILTER(R4#,INDIRECT("R3:R"&COUNTA(R:R)+2)<>R4#)

handles my collate using and indirect to grab the end of the spill per flufs answer.