creating a custom pattern/sequence based on input freq./duration and priority

Hi Excel SuperUsers,

I have been struggling with creating custom patterns based on frequency/ duration, priority as inputs....

For example let's say we have 4 letters: A, B, C, D.

Let's say that C has priority and all have frequency/duration of 1. Then I want to output the following:

C,A,C,B,C,D,C,A,C,B,C,D...etc...

Let's say that C has priority and a duration of 2 and the rest have freq./duration of 1. Desired output would be:

CC,A,CC,B,CC,D,CC,A,CC,B,CC,D...etc...

Let's say that C has priority and freq/duration of 3 while the rest have no priority but D has duration of 4 and the rest a duration of 1. Desired output would be:

CCC,A,CCC,B,CCC,DDDD,CCC,A,CCC,B,CCC,DDDD,CCC,A....etc....

Let's say that C & A both have priority. C has freq/duration of 2 and A has freq/duration of 3, D does not have priority and has duration of 4 while B has duration of 1 (no priority as well). Desired output would be:

CC,B,AAA,DDDD,CC,B,AAA,DDDD,CC,B

I have been playing with array function (index, row, rows, match, mod, countif, etc..) but can't seem to get it straight.

Is there a good source to get more educated on it? Can you provide any insight or solution?

Thanks

T

Hi

there may be a more elegant way to do it, but you could do it with nesting substitute and REPT commands. Basically substitute replaces one sub-string within another, and REPT repeats a character a number of times. so if your priorities are 2 for A, 3 for B, 4 for C and 5 for D, this formula should return the string you want from a source in A1:

Please Login or Register  to view this content.

you can then tweak the formula so that the values to replace eg "A", or the bumber of repeats eg 2, references a cell's contents, so when you change those referenced cells, the string adjusts automatically

This is a good option.

However, I guess I did not clarify that I do not need to concatenate. CCCC is more like C,C,C,C
There should be 1 letter in a cell.

Thank you for the comment!

hi
so will just using REPT be enough, or are you looking for something more?

I am not sure.

I think it is more complicated than that. REPT returns a number of repetitions of something in a single cell.

I need something like this:

for a given input for A, B, C, D let's say

Event - Duration - Priority
A - 2 - 0
B - 3 - 1
C - 1 - 0
D - 2 - 0

I need an output in an array that goes like this (by row)

B
B
B
A
A
B
B
B
C
B
B
B
D
D
B
B
B
A
A
B
B
B
etc...

I need the high priority event (1) to be repeated after every other event. If we have 2 events with priority of 1, then they will alternate and in between we will have a non-priority event (these will be just more spaced out with time)

Let me know if that makes sense

looks like you'll need a macro
this macro assumed your sheet has a 3-column named range "MyRange" with the events in the first column, duration in the second and priority in the third.

Please Login or Register  to view this content.

Thank you NickyC!

I think we are on the right track!

I have attached a workbook with a couple questions.

Thank you @NickyC!
Can I contact you in private if I have any other troubles?
How did you learn VBA? Any book suggestions?

Do you know if you can add a condition such as "If Not IsEmpty (CCell) Then..." so the macro runs only for non-blank cells within the set range?

Also, I tried to run the RRow = 100 but the cycle stops once it goes through all cells in MyRange. It does not perpetuate until RRow's given value.

Can you please look into it? I tried using some functions to limit the Macro Cycle to:

1. Only non-blank cells within "MyRange"
2. to the RRow value

...but it did not work for me. I just need to learn VBA from scratch. Let me know if you can address these issues.

Here is the modification I made so I can satisfy condition 1 above. However, I can't make it loop while it reaches a certain row. idk how to make the entire loop repeat down the column. see book attached.

Sub list_outputs2()
Dim MyPriors(), MyFreqs(), CCell As Range, x As Long, RRow As Long, HasPrior As Boolean, y As Long, a As Long, b As Long

RRow = 12 ' first row to put output data in

HasPrior = False
x = 0
For Each CCell In Range("MyRange").Columns(3).Cells
If CCell > 0 Then
HasPrior = True
ReDim Preserve MyPriors(x)
MyPriors(x) = CCell.Offset(0, -2)
ReDim Preserve MyFreqs(x)
MyFreqs(x) = CCell.Offset(0, -1)
x = x + 1

End If
Next CCell
x = x - 1

y = 0
a = 1

Do While a <= WorksheetFunction.CountA(Range("A1:A10")) + 1
b = 1
If HasPrior = True Then
Do While b <= MyFreqs(y)
Cells(RRow, 1) = MyPriors(y)
RRow = RRow + 1
b = b + 1
Loop
If y = UBound(MyPriors) Then y = 0 Else y = y + 1
End If
If Range("MyRange").Cells(a, 3) > 0 Then
Do While Range("MyRange").Cells(a, 3) > 0
a = a + 1
Loop
End If
b = 1
Do While b <= Range("MyRange").Cells(a, 2)
Cells(RRow, 1) = Range("MyRange").Cells(a, 1)
RRow = RRow + 1
b = b + 1
Loop
a = a + 1
Loop