Formula for Populating Cells From: a List of Values and List of Numbes as a Mulitple?

[MEUserII]

I have a set of unique values in column A and a set of numbers in column B to populate those values by in Column C as a multiple. Would there be a formula I could enter in to column C that would populate the values in column A as a multiple of the numbers in column B?

For example:

Row	Column A	Column B	Column C
1	Value A	1	Value A
2	Value B	2	Value B
3	Value C	3	Value B
4	Value D	4	Value C
5	Value E	5	Value C
6	Value F	6	Value C
7	Value G	7	Value D
8	Value H	8	Value D
9	Value I	9	Value D
10	Value J	10	Value D

<tbody>
</tbody>

EDIT: Could a mod correct the typo in the post title for the correct spelling of "Numbers"; that way other users can find this post in case they have the same question? The editing feature does not allow me to edit the title of posts.

 

[PhilS2520]

Hi MEUserII,

I do not see the relationship between these numbers and values. Could you tell us how the values in Col. C needs to be figured out?

For example, why is C2 value B, and C3 value B?

 

[JackDanIce]

It's unclear what you mean, can you provide a before an after example with actual values please?

Usually, if you enter a formula into a cell, the answer is returned in the *same* cell.
So if you enter a formula into column C, then column C will return output (if possible), not column A

If all the values are numerical, can you not use something like

=A1*B1

or

=C1*B1

?

 

[MEUserII]

Hi MEUserII,

I do not see the relationship between these numbers and values. Could you tell us how the values in Col. C needs to be figured out?

For example, why is C2 value B, and C3 value B?

Column C represents the "Column B number" of times the "Column A value" should be listed in Column C.

It's unclear what you mean, can you provide a before an after example with actual values please?

Usually, if you enter a formula into a cell, the answer is returned in the *same* cell.
So if you enter a formula into column C, then column C will return output (if possible), not column A

If all the values are numerical, can you not use something like

=A1*B1

or

=C1*B1

?

Specifically:
______|(Column A)x(Column B)= ("Number in Column B" of "Values in Column A" is to be listed in Column C)
Row 1: (Value A)x(1) = 1 of Value A in Column C (C1)
Row 2: (Value B)x(2) = 2 of Value B in Column C (C2, C3)
Row 3: (Value C)x(3) = 3 of Value C in Column C (C4, C5, C6)
Row 4: (Value D)x(4) = 4 of Value D in Column C (C7, C8, C9, C10)

Note: The values in column A are words (not numbers); specifically, an example like:
Row 1: (Word A)x(1) = 1 of Word A in Column C (C1)
Row 2: (Word B)x(2) = 2 of Word B in Column C (C2, C3)
Row 3: (Word C)x(3) = 3 of Word C in Column C (C4, C5, C6)
Row 4: (Word D)x(4) = 4 of Word D in Column C (C7, C8, C9, C10)

 

[MEUserII]

I have posted a longer sample, so it's more helpful to see what the end result should look like in Column C by the formula I am asking about.

Row	Column A	Column B	Column C
1	Alpha	2	Alpha
2	Beta	3	Alpha
3	Gamma	4	Beta
4	Delta	5	Beta
5	Epsilon	6	Beta
6	Zeta	7	Gamma
7	*	*	Gamma
8	*	*	Gamma
9	*	*	Gamma
10	etc.	etc.	Delta
11			Delta
12			Delta
13			Delta
14			Delta
15			Epsilon
16			Epsilon
17			Epsilon
18			Epsilon
19			Epsilon
20			Epsilon
21			Zeta
22			Zeta
23			Zeta
24			Zeta
25			Zeta
26			Zeta
27			Zeta

<tbody>
</tbody>

 

[MEUserII]

Anyone know of a formula that would accomplish that would answer this question?

 

[JackDanIce]

Not sure how as a formula, as a macro try:

Sub Expand()

Dim x as Long
Dim y as Long: y = 1
Dim arr() as Variant

Application.ScreenUpdating = False

x = Cells(Rows.Count, 1).End(xlUp).Row
arr = Cells(1,1).Resize(x, 2).Value

For x = lbound(arr, 1) to ubound(arr, 1)
  Cells(y, 4).Resize(arr(x, 2)).Value = arr(x,1)
  y = Cells(1, 4).End(xlUp).Row + 1
Next x

Application.ScreenUpdating = False

Erase arr

End Sub