Creating a list according to partial duplicates & Incrementing delimiter in TEXTJOIN

Hello guys,

coming here for a second time with mind boggling problem (for me

As you can see in the attached file I have a list of product models.
Full list is 240 000 products and part of them are included in series of products. Totally about 9k+ series.
I have to prepare a list of ID`s of other products, from the same series, for each product.
The list can include the current product at first place in the list or can be excluded.
The list has to be in one cell as the file I`m working on is already with 90 columns and to add another hundred (in some cases) is not very friendly.

A product matches to a series if columns C,D,E,F,G match. I added a column H to visualize it.

are you looking for a single formula for column H, where the formula will be in cell H1, and it will give a header value (DUPS) and automatically concatenate each row...

try this in cell H1:

Please Login or Register  to view this content.

sample attached

It is out of the initial scope of the post "Creating a list according to partial duplicates" Started by gotmort‎, Yesterday 07:20 AM

And to give it a proper name I decided to go on a new Thread.

MOD EDIT: Moved back to the original thread - continue here, please.


After managing the first part in the previous thread we got the result
220005;220033;210077;210085;220038;220016;160050

And now I reached the point where the final result is clear.

It should look like this:

a:7:{i:0;i:220005;i:1;i:220033;i:2;i:210077;i:3;i:210085;i:4;i:220038;i:5;i:220016;i:6;i:160050;}

The first number should count the amount of joined cells.
The numbers between the cells should increase by one starting from 0


The attached file

Column I
Textjoin where I also put the required delimiter ;i:0;i: / this is the part that I'm unable to figure out the 0 should be incrementing and should start form 0 (in the attached example I added 13) as a part of the delimiter to make the rest work
Column J
I add the start and the end of the cell as required / The first number should be equal to the total amount of ID`s it is also equal to the last counter +1
Column K
I remove everything up to the last counter /easy because all id`s of products are 6 digits
Column L
I remove everything before the last counter / I remove everything before the last :

Column I is the problem. The auto incrementing part.
Id`s which I'm joining might not be in any order and can be anywhere from 2 up to 1000
Tried with concatenated text in the delimiter. Didnt managed to make it work.
Any idea is it at all possible?

Please share the formula you came up with.

Which Office version do you have? Is 2020 a Mac version or a release number?