How can I modify my array TEXTJOIN formula to output only unique values

This is my worksheet:
Capture1.PNG

This is the formula in question:

Please Login or Register  to view this content.

Untitled1.png

My goal is to collect all markets in column I that belong to the same Invoice number, into one cell in column J.
I made this wildcard array TEXTJOIN formula that does everything that I want it to do, exept that it outputs all the values it finds; when I want it to only output the unique values.

Desired results:
Capture2.PNG

How can I modify this formula to accomplish this?

Welcome to the forum.
please attach sample file, as per forum rule https://www.excelforum.com/forum-rul...rum-rules.html

Try this:

Formula:

Please Login or Register  to view this content.

Checkout here as well: https://www.ablebits.com/office-addi...-values-excel/

That formula is able to output unique values, but the issue is that its reference cells are static and has a constant range lenght. I have found many different formulas online that accomplishes that. The real problem I have is that I can't figure out how to combine a formula like yours which outputs unique values, with a formula like mine which has dynamic cell references that are based on which rows have the same value in column C.

Ok, you wanted to have unique values only in row J3 for all smiliar values in col C (WALLENIUS WILHELMSEN LINES AS NORWAY, BRANCH SWEDEN = 22 rows, it may vary)

My bad, I meant column E, not C. Othervise you are correct, the invoice has constituent invoices with the same invoice number. Every constituent invoice(each row) has one market, and I want to collect all markets from the invoice's contistituent parts into column J.

I also realised that my original formula should trigger like this (On New Invoice Number)

Please Login or Register  to view this content.

Instead of this (On New Vendor)

Please Login or Register  to view this content.

You can see in my images that J3 contains all the markets from column I on the rows with invoice number: SEGOTFSI/1027782.
And J281 contains all the markets from column I on the rows with invoice number: F142420

That's how I want the formula to work. Exept that instead of showing every market in J, I want it to just show the unique markets.
So when invoice: SEGOTFSI/1027782 contains these markets:
PL, PL, PL, PL, PL, PL, PL, PL, PL, PL, IT, GB, GB, GB, GB, GB, GB, SE, GB, IT, PL, SE
I want it to be showed in J3 as:
PL, IT, GB, SE

Hi,

First of all, you absolutely should not use entire column references within an array formula. I appreciate that your range is dynamic and subject to change, and that, by referencing entire columns, you are 'covering your bases'. However, array formulas calculate over all cells within the ranges passed to them, whether beyond the last-used cells in those ranges or not. As such, an array formula which references an entire column is being forced to calculate more than a million more cells than one which references, say, 6000 rows. And that's for just one instance of that formula.

Either use dynamic ranges or reduce the end row being referenced to a suitably low, though sufficient, value.

In J3:

=IF(C3<>C2,TEXTJOIN(", ",1,IF(FREQUENCY(IF(ISNUMBER(SEARCH(E3,E$3:E$6000)),MATCH(I$3:I$6000,I$3:I$6000,0)),ROW(I$3:I$6000)-MIN(ROW(I$3:I$6000))+1),I$3:I$6000,"")),"")

Regards

Hey Victor,

I'd create a Helper Column with this formula and only use your states if the helper is =1.

=COUNTIFS(I$2:I3,I3,E$2:E3,E3)

See attached with the helper column.

Need Helper Column.xlsx

XOR LX, your formula worked but it was very resourse intensive, excel bacame practically unusable to me due to the sluggishness when using it .

I tried MarvinP's Idea and this is the solution I came up with

Capture now.PNG

Helper #1 column contains this formula:

Please Login or Register  to view this content.

Helper #2 column formula:

Please Login or Register  to view this content.

Markets formula:

Please Login or Register  to view this content.

I had to include Helper #2 because otherwise the Markets formula treat 1, 10-19, 21, 31, etc. as conditions to search for instead of just 1.

One weird thing I noticed is the when I use filters the Markets formula doesn't show the result even thought its still in the cell, so you have to reapply it.

Originally Posted by Victorjo

XOR LX, your formula worked but it was very resourse intensive, excel bacame practically unusable to me due to the sluggishness when using it .

I take it you heeded my advice about not using entire column references?

With the formula I posted (upper row reference reduced to 6000) on the workbook you supplied, a full calculation took barely no time at all.

Regards

XOR LX, I did use your formula with reference 3 to 6000, but it was still very resourse intensive. I even whent and changed all my other formulas in my document which referenced the entire column to only reference 3 to 6000. All this time I thought that referencing the entire column meant it only looked at all active rows, so thank you for that advice