View Full Version : [SOLVED:] Merge variable text
9!GR@BzyQ37b
04-21-2022, 08:21 AM
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
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
04-21-2022, 03:40 PM
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
04-22-2022, 04:20 AM
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
04-22-2022, 04:25 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.