Merge variable text

[9!GR@BzyQ37b]

Hi,

I want to merge texts where the number of texts depends on the same article number.

So sometimes the article number occurs 5 times and 5 texts have to be merged. But sometimes the article number occurs 3 times. And so 3 texts have to be merged.
Is it possible to run this with a vba code?

In the example it will be clear what the result should be (I hope).

Thanks in advance!

Peter
29661

[snb]

Vermijd in Excel samengevoegde cellen
Gebruik in een werkblad altijd kolom A en Rij 1.
Na verwijdering van lege rijen en kolommen:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
For j = 2 To UBound(sn)
.Item(sn(j, 1)) = IIf(.Item(sn(j, 1)) = "", "Dit artikel is onderdeel van combipakketten: ", .Item(sn(j, 1)) & ", ") & sn(j, 3) & " " & sn(j, 5)
Next

ReDim st(.Count, 2)
For j = 0 To .Count - 1
st(j, 0) = .keys()(j)
st(j, 1) = .items()(j)
Next
End With

Sheet1.Cells(10, 2).Resize(UBound(st) + 1, 2) = st
End Sub

[p45cal]

Maybe:
In one cell (I7):

=UNIQUE(C4:C42)
This should spill down.

In the cell to the right:

="Dit artikel is onderdeel van combipakketten: " & TEXTJOIN(", ",TRUE,FILTER($E$4:$E$42&" " &$G$4:$G$42,$C$4:$C$42=I7)) & "."
copied down as far as the other formula spilt.

I'm being lazy, because it's possible to do all the above with a single formula in only one cell.

Anyway, see attached.

You don't need a separate column with the EAN in brackets, the 2nd formula can incorporate them:

="Dit artikel is onderdeel van combipakketten: " & TEXTJOIN(", ",TRUE,FILTER($E$4:$E$42&" (" &$F$4:$F$42 & ")",$C$4:$C$42=I7)) & "."

[9!GR@BzyQ37b]

Maybe:
In one cell (I7):

=UNIQUE(C4:C42)
This should spill down.

In the cell to the right:

="Dit artikel is onderdeel van combipakketten: " & TEXTJOIN(", ",TRUE,FILTER($E$4:$E$42&" " &$G$4:$G$42,$C$4:$C$42=I7)) & "."
copied down as far as the other formula spilt.

I'm being lazy, because it's possible to do all the above with a single formula in only one cell.

Anyway, see attached.

You don't need a separate column with the EAN in brackets, the 2nd formula can incorporate them:

="Dit artikel is onderdeel van combipakketten: " & TEXTJOIN(", ",TRUE,FILTER($E$4:$E$42&" (" &$F$4:$F$42 & ")",$C$4:$C$42=I7)) & "."

[9!GR@BzyQ37b]

Hi p45cal,

Thank you for your solution. I've tried the vba code of snb and that's working fine. And is just what I need. So I will use that code.

BR,
Peter