Using TEXTJOIN with INDEX/UNIQUE/SEQUENCE to combine range in batches

[Brawnystaff]

I am using an Excel formula to combine values in a range in "batches" for use elsewhere using the following formula:

Please Login or Register  to view this content.

In example above, looking to combine values in rows 2 to 101 in segments the user designates. Basically, I enter in the formula and copy it down the range until all values are displayed in the TEXTJOINed cells. It works great if the total number of values in the range is evenly divisible by the sequence number , but if it is not, it only gives a portion of of the range and then gives an REF error.

Any ideas how to correct? See attached sample workbook. Note sample does not include any duplicate values, but in real-world data, duplicate data could exist in the range anywhere.

[Joske920]

Hi,

Its because you go out of range of the unique function it only has 51 unique values and so you go out of bounds of the array it makes

[Brawnystaff]

That was just a quick sample, but even if I adjust the formula range to the actual range in the Worksheet ($A$2:$A$51), it still has the problem when dragged down and not having a divisible batch number.

[Joske920]

can you try this formula?

Please Login or Register  to view this content.

[Brawnystaff]

Interesting use of an IF statement. That worked. Thanks.