Get Spill-result to reference correct row #

Question

As a result of this closed topic I started wondering myself the following:

Let's say we have data like this:

	A
1	a
2	b
3	c
4	a
5	b
6	c
7	g
8	h
9	i

I want to divide the list into 3 (I used TEXTJOIN) and check which of the 3 of these is unique. I used a combination of MATCH and COUNTIF (and SEQUENCE) for this.

Question: I managed to get that correct, but wanted to get the 3 textjoin-results all at once as a spill range.

In the picture below you can see my results of attempts, but I couldn't get the TEXTJOIN to reference the correct column in it's spill-result. What is missing, or what am I doing wrong?

user_2872922 · Answer

Not sure what you want for output.

If all you want is to determine which is/are unique entries, just use the UNIQUE function. Note the exactly_once argument.

For example:

C1: =OR(B1=UNIQUE($B$1:$B$3,,TRUE))

and fill down, but the formula will fill down automatically.

Edit: I don't know how to get the TEXTJOIN function to spill down in groups of three like you want. However, to be able to enter the formula just once, and have the results appear in column B, and have that adjust as you add/remove entries from column A, you can use a Table structure.

In that case, row 1 would be the column headers, and the formula in B2 would be:

=IFERROR(TEXTJOIN(",",TRUE,INDEX([Column1],SEQUENCE(3)+(ROW()-ROW(Table2[#Headers]))*3-3)),"")`

No need to fill down.

Edit2:

To create a list of unique (listed only once) values from Column B in Column C, you can use the UNIQUE function. However, due to limitations on SPILL functions within a Table, this column cannot be part of the table.

In the example below, where Column C is NOT part of the table:

B2: =IFERROR(TEXTJOIN(",",TRUE,INDEX([Column1],SEQUENCE(3)+(ROW()-ROW(Table2[#Headers]))*3-3)),"")
C2: =UNIQUE(Table2[Column2],,TRUE)

Edit3:

If you were OK with omitting columnB, and reporting the unique triplets in separate cells instead of concatenated, you could do that with the UNIQUE function:

or, without a table,

=UNIQUE(INDEX($A:$A,SEQUENCE(COUNTA($A:$A)/3,3,2)),,TRUE)

Note that the start argument in the SEQUENCE function represents the first line of data in column A, (2 in this example)

Get Spill-result to reference correct row #

1 Answers