0

Could someone help me with this formula:

arrayformula(TEXTJOIN(", ",TRUE,unique(IF(Sheet1!$A:$A=A2,Sheet1!$B:$B,""))))

So basically, I've got the name of Merchants in 'Sheet1'!$A:$A and the name of the Campaign Name Period they're working on in 'Sheet1'!$B:$B I want to generate the merged Campaign Name Period, I want to make this line above to work with another arrayformula so I don't have to copy this formula to all cells in the column, I've tried to make it like this.

arrayformula(arrayformula(TEXTJOIN(", ",TRUE,unique(IF('Sheet1!$A:$A=$A2:$A,Sheet1!$B:$B,"")))))

but still, it won't work, it gives me this "Error Array arguments to EQ are of different size."

Here's a link to the sample spreadsheet

Osm
  • 2,699
  • 2
  • 4
  • 26
byrmn__
  • 59
  • 6

2 Answers2

1

Try something like

=arrayformula(regexreplace({unique(Sheet1!A2:A), trim(transpose(query(if((transpose(unique(Sheet1!A2:A))=Sheet1!A2:A)*len(Sheet1!A2:A),Sheet1!B2:B&", ",),,50000)))},",$", ))

Note that this formula will also create the unique merchant names.

UPDATE:

Using the new map() or byrow() functions it is now possible to skip the intermediate step of creating a unique list. Try

=unique({Sheet1!A:A, map(Sheet1!A:A,  lambda( item, textjoin(", ", 1, unique(filter(Sheet1!B:B, Sheet1!A:A=item)))))})

or

=unique({Sheet1!A:A, map(Sheet1!A:A,  lambda( item, textjoin(", ", 1, unique(filter(Sheet1!B:B, Sheet1!A:A=item)))))})

More info: MAP(), BYROW()

JPV
  • 26,499
  • 4
  • 33
  • 48
  • sorry man, i found out that this formula didn't extract the unique "Campaign Name Period", for example there are 2 entry of "Garuda Voucher" and both of it has the same campaign name period which is "10.10 (Sep W4)" instead of showing 10.10 (Sep W4) in the result, it show "10.10 (Sep W4), 10.10 (Sep W4)", would you once again check it and give me solution for this, i will very appreciate your help man! – byrmn__ Sep 14 '22 at 04:41
  • Quick fix would be to use a helper tab with the formula =unique(Sheet1!A:B). Then reference that sheet in the formula I posted earlier. – JPV Sep 14 '22 at 08:07
  • yes, it worked, but if it's possible I want to make it work with 1 line formula, but i will use this method until i found another way to do it. – byrmn__ Sep 15 '22 at 06:29
  • @byrmn__: I update the answer with a one-formula-solution. See if that works for you? – JPV Sep 27 '22 at 08:53
  • Awesome!! both work flawlessly, you are the best man, thanks for your help! – byrmn__ Sep 28 '22 at 04:55
  • Hi @JPV: would you mind to check my other tread and maybe if you have a solution for me I really appreciate it : https://stackoverflow.com/questions/73877040/is-there-any-formula-that-i-can-use-to-how-to-show-up-value-month-in-between-f – byrmn__ Sep 28 '22 at 06:28
0

Try

arrayformula(arrayformula(TEXTJOIN(", ",TRUE,unique(IF('Pivot Oct'!$B2:$B=$B2:$B,'Pivot Oct'!$F2:$F,"")))))
Osm
  • 2,699
  • 2
  • 4
  • 26