repeat/concat data in a cell based on values in other cells

[Troyrob]

I am trying to fill in/update cells based on the contents of a data cell and repeat that pattern for N times where N is the value of another cell. The data cell would contain a string and the match condition would be a the contents of another cell. For example:

Output	A	A	A
Data	ABC	DEF	Q	SX	VVV
Repeat	3
Match string	A

<tbody>
</tbody>

Output	A	AA	AA	A
Data	ABC	A	RES	Y1W	E
Repeat	3
Match String	A

<tbody>
</tbody>

Output	A	AA	AAA	AA	A
Data	A	A1	ABC	Qwe	KK
Repeat	3
Match String	A

<tbody>
</tbody>

Output	B	B	BBB	BB	BB
Data	BOO	A	BOB	NBV	456
Itterations	3
Match String	B

<tbody>
</tbody>

I don't know if this is possible to do with just formulas, or if it requires VB, or maybe not even possible in general. Hopefully I explained it well.

Thanks in advance for your input. <style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>

 

[Eric W]

Welcome to the board.

It seems possible, but I'm not entirely sure of your requirements. Consider this:

	A	B	C	D	E	F	G	H	I
1	Output			A	A	A
2	Data			ABC	DEF	Q	SX	VVV
3	Repeat			3
4	Match string			A
5
6	Output			A	AA	AA	A
7	Data			ABC	A	RES	Y1W	E
8	Repeat			3
9	Match String			A
10
11	Output			A	AA	AAA	AA	A
12	Data			A	A1	ABC	Qwe	KK
13	Repeat			3
14	Match String			A
15
16	Output			B	B	BBB	BBB	BBB	B
17	Data			BOO	A	BOB	NBV	456
18	Itterations			3
19	Match String			B

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D1 =REPT($D4,LEN(B2)-LEN(SUBSTITUTE(B2,$D4,"")))&REPT($D4,LEN(C2)-LEN(SUBSTITUTE(C2,$D4,"")))&REPT($D4,LEN(D2)-LEN(SUBSTITUTE(D2,$D4,""))) <tbody> </tbody>

<tbody>
</tbody>

I added 2 empty columns B:C to make this work. You can hide them if you want. Then put in the D1 formula, and drag to the right as far as needed. Then copy to D6:I6, D11:I11, and D16:I11. Notice that my results in G16:I16 don't match yours. So either you have a few typos, or I don't understand your question.

Let me know.


Edit: Shoot, I just realized that you said N times, and this formula is hardcoded for N=3. To do that, you'd need either the new CONCAT function (in some versions of Excel 2016 or Live), or a VBA function. I'll see if I can get a VBA function for you.

 

[Troyrob]

I am using the latest version of Excel if that helps...

 

[Troyrob]

The 4th example is a bit of a twist - the match string occurs multiple times in the data, so ideally it would trigger the repeating output once for each match. So if you were to 'unroll' the output it would look like this:

B B B
(no matches)
BB BB BB
B B B
(no matches)
(no matches)

and when you roll them back up the output is:

B B BBB BBB BBB B

My apologies if I am not describing that well. Your reply is very helpful, it is way beyond my current skill level

Thank you
Troy

 

[Eric W]

So the results for your 4th example are what I had in post # 2. So I guess I did see what you wanted. Let's try a UDF. Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. In the sheet that opens, paste this code:

Public Function MyConcat(MyStr As String, MyCnt As Long, MyRng As Range) As String
Dim i As Long, MyTot As Long, c As Range

    If MyRng.Rows.Count > 1 And MyRng.Columns.Count > 1 Then
        MyConcat = "The range must be 1-D"
        Exit Function
    End If
    
    MyTot = 0
    i = 1
    For Each c In MyRng
        If i >= MyRng.Cells.Count - MyCnt + 1 Then MyTot = MyTot + Len(c.Value) - Len(Replace(c.Value, MyStr, ""))
        i = i + 1
    Next c
    
    MyConcat = WorksheetFunction.Rept(MyStr, MyTot)

End Function

Close the VBA editor (Alt-Q or the red X). Now go to your sheet. Assuming it looks like this:

	A	B	C	D	E	F	G
1	Output	A	A	A
2	Data	ABC	DEF	Q	SX	VVV
3	Repeat	3
4	Match string	A
5
6	Output	A	AA	AA	A
7	Data	ABC	A	RES	Y1W	E
8	Repeat	3
9	Match String	A
10
11	Output	A	AA	AAA	AAA	AA	A
12	Data	A	A1	ABC	Qwe	KK
13	Repeat	4
14	Match String	A
15
16	Output	B	B	BBB	BBB	BBB	B
17	Data	BOO	A	BOB	NBV	456
18	Itterations	3
19	Match String	B

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B1 =MyConcat($B4,$B3,$B2:B2) <tbody> </tbody>

<tbody>
</tbody>

You can use the formula in B1. As you drag it to the right, you'll get the results you want. Copy B1 to the other cells as needed. Let me know how it works.

 

[Troyrob]

That seems to be the ticket!!!

Thank you so much for your help.
Troy

 

[Eric W]

Glad to help.

Have a good weekend.

 

[Peter_SSs]

I am using the latest version of Excel if that helps...

As Eric mentioned, this means you could use a worksheet function using CONCAT if you want.

IF the 'Match string' is always only a single character, try this copied across.

Excel Workbook
	A	B	C	D	E	F
1	Output	A	A	A
2	Data	ABC	DEF	Q	SX	VVV
3	Repeat	3
4	Match string	A
5
6	Output	A	AA	AA	A
7	Data	ABC	A	RES	Y1W	E
8	Repeat	3
9	Match String	A
10
11	Output	A	AA	AAA	AA	A
12	Data	A	A1	ABC	Qwe	KK
13	Repeat	3
14	Match String	A
15
16	Output	B	B	BBB	BBB	BBB
17	Data	BOO	A	BOB	NBV	456
18	Itterations	3
19	Match String	B
20
21	Output	B	B	BBB	BBBB	BBB
22	Data	BOO	A	BOB	NBV	456
23	Itterations	4
24	Match String	B
Repeat

IF the 'Match string' can be multiple characters, then change to ..

Excel Workbook
	A	B	C	D	E	F
1	Output	AB	AB	AB	ABAB	AB
2	Data	ABC	DEF	Q	SXAB	VVV
3	Repeat	4
4	Match string	AB
Repeat (2)