Formula to generate array with specified number of different values

[weisslerb]

Hello forum.

I have a question about generating an array based on a certain set of parameters.

In the example table at the bottom of this post, the first row of numbers (1, 2, and 3) represent desired values, and the second row (4, 3, and 2) represent the desired frequency of each of those respective values.

In other words, I would like to generate an array with four 1s, three 2s, and two 3s: {1,1,1,1,2,2,2,3,3}

Is there a formula that could dynamically generate such an array?? (An implementation without VBA would be ideal.)

So far I've figured out a formula to create three separate arrays ({1,1,1,1} and {2,2,2} and {3,3}; each separate formulas in adjacent cells) -- but there seems to be no easy way of joining/concatenating those arrays...

Your help is appreciated!

[TABLE="class: grid, width: 425, align: left"]
<tbody>[TR]
[TD="align: center"]Values:[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]Frequency:[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

I'm not 100% clear what you want to generate; what formula were you using to generate the individual arrays?

WBD

 

[weisslerb]

Thanks for the reply.

I want ultimately to generate this array: {1,1,1,1,2,2,2,3,3} (i.e., four 1s, three 2s, and two 3s)

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]



Here would be my formula to generate each of the individual arrays (assuming that the example table above starts A1):

• {=(A1*ROW(INDIRECT(CONCATENATE("1:",A2))))/ROW(INDIRECT(CONCATENATE("1:",A2)))}
• {=(B1*ROW(INDIRECT(CONCATENATE("1:",B2))))/ROW(INDIRECT(CONCATENATE("1:",B2)))}
• {=(C1*ROW(INDIRECT(CONCATENATE("1:",C2))))/ROW(INDIRECT(CONCATENATE("1:",C2)))}

These formulas yield the following results:

• ={1;1;1;1}
• ={2;2;2}
• ={3;3}

However, I can't figure out how to join the three results into my desired array — {1,1,1,1,2,2,2,3,3}. Does this make sense?

 

[wideboydixon]

I had to add a helper row underneath but this generates the array:

Book1
	A	B	C
1	1	2	3
2	4	3	2
3	1	5	8
4	1
Sheet1

Cell Formulas
Range	Formula
B3	=A$3+A$2
C3	=B$3+B$2
A4	=LOOKUP(ROW(INDIRECT("1:"&SUM($A$2:$C$2))),$A$3:$C$3,$A$1:$C$1)

WBD

 

[Rick Rothstein]

I know you said you would rather not use VBA, but in case you change your mind, here is a UDF (user defined function) that will output the array you want...

[table="width: 500"]
[tr]
	[td]Function DynamicArray(ParamArray NumberCommaCount()) As Variant
  Dim X As Long, Txt As String
  For X = LBound(NumberCommaCount) To UBound(NumberCommaCount) Step 2
    Txt = Txt & Application.Rept(NumberCommaCount(X) & ",", NumberCommaCount(X + 1))
  Next
  DynamicArray = Evaluate("{" & Left(Txt, Len(Txt) - 1) & "}")
End Function[/td]
[/tr]
[/table]

The function is entered normally and takes a variable number of arguments... a number to be repeated followed by the number of times to repeat that number, followed by the next number to be repeated followed by the number of times to repeat that number and so on. So, for you posted situation, you could use this formula...

=DynamicArray(1,4,2,3,3,2)

or this one...

=DynamicArray(A1,A2,B1,B2,C1,C2)

Remember, you enter this formula normally (do not use CTRL+SHIFT_ENTER).

 

[weisslerb]

Thanks. This exactly right.

Is there a way to pass this result into other conventional formulas such as =SUM() -- right now if I sum on A4 (your formula) I get a result of 1, instead of 16 (1+1+1+1+2+2+2+3+3)?

 

[weisslerb]

I know you said you would rather not use VBA, but in case you change your mind, here is a UDF (user defined function) that will output the array you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function DynamicArray(ParamArray NumberCommaCount()) As Variant
  Dim X As Long, Txt As String
  For X = LBound(NumberCommaCount) To UBound(NumberCommaCount) Step 2
    Txt = Txt & Application.Rept(NumberCommaCount(X) & ",", NumberCommaCount(X + 1))
  Next
  DynamicArray = Evaluate("{" & Left(Txt, Len(Txt) - 1) & "}")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

The function is entered normally and takes a variable number of arguments... a number to be repeated followed by the number of times to repeat that number, followed by the next number to be repeated followed by the number of times to repeat that number and so on. So, for you posted situation, you could use this formula...

=DynamicArray(1,4,2,3,3,2)

or this one...

=DynamicArray(A1,A2,B1,B2,C1,C2)

Remember, you enter this formula normally (do not use CTRL+SHIFT_ENTER).

Thank you for the code, Rick. I just tried and this works great as well!

 

[wideboydixon]

Thanks. This exactly right.

Is there a way to pass this result into other conventional formulas such as =SUM() -- right now if I sum on A4 (your formula) I get a result of 1, instead of 16 (1+1+1+1+2+2+2+3+3)?

I somehow managed to drop the array formula part. A4 and any other formula using it needs to be entered with Ctrl+Shift+Enter. If you're going to use SUM() though, you could use SUMPRODUCT to start with:

Book1
	A	B	C
1	1	2	3
2	4	3	2
3	1	5	8
4	16	16
Sheet1

Cell Formulas
Range	Formula
B3	=A$3+A$2
B4	=SUMPRODUCT($A$1:$C$1,$A$2:$C$2)
C3	=B$3+B$2
A4	{=SUM(LOOKUP(ROW(INDIRECT("1:"&SUM($A$2:$C$2))),$A$3:$C$3,$A$1:$C$1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Marcelo Branco]

Thanks. This exactly right.

Is there a way to pass this result into other conventional formulas such as =SUM() -- right now if I sum on A4 (your formula) I get a result of 1, instead of 16 (1+1+1+1+2+2+2+3+3)?

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][td]

Sum of all elements​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

4​

[/td][td]

3​

[/td][td]

2​

[/td][td][/td][td="bgcolor:#B8CCE4"]

16​

[/td][/tr]
[/table]

Array formula in E2
=SUM(INDEX($A$1:$C$1,N(IF(1,IFERROR(1+MATCH(COLUMN($A$1:INDEX($1:$1,SUM($A$2:$C$2)))-1,SUBTOTAL(9,OFFSET($A$2,,,,COLUMN($A$2:$C$2)-COLUMN($A$2)+1))),1)))))
Ctrl+Shift+Enter

M.