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

T

Troyrob

New Member
Joined
Mar 30, 2018
Messages
4
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]ABC[/TD]
[TD]DEF[/TD]
[TD]Q[/TD]
[TD]SX[/TD]
[TD]VVV[/TD]
[/TR]
[TR]
[TD]Repeat[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Match string[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]AA[/TD]
[TD]A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]ABC[/TD]
[TD]A[/TD]
[TD]RES[/TD]
[TD]Y1W[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Repeat[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Match String[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD]A[/TD]
[TD]AA[/TD]
[TD]AAA[/TD]
[TD]AA[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]A[/TD]
[TD]A1[/TD]
[TD]ABC[/TD]
[TD]Qwe[/TD]
[TD]KK[/TD]
[/TR]
[TR]
[TD]Repeat[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Match String[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Output[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]BBB[/TD]
[TD]BB[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]BOO[/TD]
[TD]A[/TD]
[TD]BOB[/TD]
[TD]NBV[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]Itterations[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Match String[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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-ignore:padding; 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>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Welcome to the board.

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

ABCDEFGHI
OutputAAA
DataABCDEFQSXVVV
Repeat
Match stringA
OutputAAAAAA
DataABCARESY1WE
Repeat
Match StringA
OutputAAAAAAAAA
DataAA1ABCQweKK
Repeat
Match StringA
OutputBBBBBBBBBBBB
DataBOOABOBNBV
Itterations

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]456[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]Match String[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D1[/TH]
[TD="align: left"]=REPT($D4,LEN(B2)-LEN(SUBSTITUTE(B2,$D4,"")))&REPT($D4,LEN(C2)-LEN(SUBSTITUTE(C2,$D4,"")))&REPT($D4,LEN(D2)-LEN(SUBSTITUTE(D2,$D4,"")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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:

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:

ABCDEFG
OutputAAA
DataABCDEFQSXVVV
Repeat
Match stringA
OutputAAAAAA
DataABCARESY1WE
Repeat
Match StringA
OutputAAAAAAAAAAAA
DataAA1ABCQweKK
Repeat
Match StringA
OutputBBBBBBBBBBBB
DataBOOABOBNBV
Itterations

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]456[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FAFAFA"]Match String[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=MyConcat($B4,$B3,$B2:B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
Troyrob said:
I am using the latest version of Excel if that helps...
Click to expand...
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
ABCDEF
1OutputAAA
2DataABCDEFQSXVVV
3Repeat3
4Match stringA
5
6OutputAAAAAA
7DataABCARESY1WE
8Repeat3
9Match StringA
10
11OutputAAAAAAAAA
12DataAA1ABCQweKK
13Repeat3
14Match StringA
15
16OutputBBBBBBBBBBB
17DataBOOABOBNBV456
18Itterations3
19Match StringB
20
21OutputBBBBBBBBBBBB
22DataBOOABOBNBV456
23Itterations4
24Match StringB
Repeat



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

Excel Workbook
ABCDEF
1OutputABABABABABAB
2DataABCDEFQSXABVVV
3Repeat4
4Match stringAB
Repeat (2)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Comparing two columns with comma separated string values (match) and output: matching string/text
Replies
6
Views
681
Fluff
Fluff
B
  • Question Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
572
B-radK
B
O
  • Question Question
sum or subtract for the same cell based on match DATE
Replies
0
Views
328
Omran Y
O
T
  • Solved
match based on headers and cell and sheet name to summing amounts
Replies
3
Views
557
DanteAmor
DanteAmor
T
  • Question Question
delete duplicates items and keep duplicates others based on matching partial text and value
Replies
3
Views
511
tubrak
T

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top