Repetition of values in Column x times upwards

Salem 80 · Jan 14, 2018

Good day to all,
Hoping for help with the following issue,
Column A contains the original data; Column B determines the number of needed repetitions for each value in column A, Column C needs to shows the results.
What formula can be used for this operation to work.
Thank you

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63"]Data [/TD]
[TD="class: xl63"]Repetition[/TD]
[TD][/TD]
[TD="align: left"]Results[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"]8[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD="class: xl64"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]13[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]13
[/TD]
[/TR]
</tbody>[/TABLE]

Please note that the repetition for each value in Column A is going upwards, the space needed for the number of repetitions mentioned in column B are already available ( this is how the original data was created at start)

Tetra201 · Jan 14, 2018

If the exact space is allocated (C2:C14 for the posted dataset), then try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=INDEX(A2:$A$15,MATCH(TRUE,ISNUMBER(B2:$B$15),0))

Rick Rothstein · Jan 14, 2018

Tetra201 said:
If the exact space is allocated (C2:C14 for the posted dataset), then try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=INDEX(A2:$A$15,MATCH(TRUE,ISNUMBER(B2:$B$15),0))

I doubt this formula is better than yours, but I think it is an interesting alternative. Like your formula, it too needs to be array-entered**

=0+(VLOOKUP("* ",TEXT(A2:A$15,"0 ;;;"),1,0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

EDIT NOTE:
------------------
I would also note that if you changed your formula to this...

=INDEX(A2:$A$15,MATCH(TRUE,ISNUMBER(A2:$A$15),0))

then, like for my formula, the OP's values in Column B would no longer be necessary.

Tetra201 · Jan 14, 2018

Rick,

Really nice alternatives -- with one caveat: they work only for numerical data in Column A (which seems to be the case).

Rick Rothstein · Jan 14, 2018

Tetra201 said:
Rick,

Really nice alternatives

Thanks

Tetra201 said:
...with one caveat: they work only for numerical data in Column A (which seems to be the case).

True... I decided to take the OP at his word and assume if he posted numbers, then he had numbers. If he were to only have text (no numbers at all), then then my formula becomes a really simple normally-entered formula...

=VLOOKUP("*",A2:A$15,1,0)

If, on the other hand, the column can contain a mix of text and numbers, then we are back to an array-entered formula again...

=VLOOKUP("* ",TEXT(A2:A$15,"0 ;;;@ "),1,0)

which returns any numbers as text. There does not seem to be a simple VLOOKUP formula solution when there is a mix of numbers and text and the numbers must remain as numbers.

Salem 80 · Jan 16, 2018

Thank you very much.
The two formulas by Tetra201 and by Rick Rothstein worked lovely, with the following notes regarding the later:

The Numerical values in column A are returned to the nearest whole integer, for example a value of 0.4 we be repeated as a 0.0, a value of 1.7 will be repeated as a 2.0, as so on. I should have mentioned that the original data is not all integers and does include rational or fractional numbers.
The formula did not recognize a Zero value in Column A, in this case, the first value coming after the zero in the column will be repeated in the cells originally dedicated for the zero repetition.

Again, thank you very much, I am learning from your sharing.

Salem 80 · Jan 16, 2018

Hello,
I have more than one set of data, as in several groups with each group having its own original set of raw data in one column and a column dedicated for the times of repetition and the third column for data after repetition as discussed in the earlier posts ( Group (1) has column A,B,&C, Group (2) has columns D,E, &F, Group (3) has columns G,H, & I, etc..).
I have to use the data from the different groups containing the final repeated sets ( Columns C,F,&I) in common mathematical operations ( adding, subtracting, multiplying, etc..) but the result of such operations are giving a (#VALUE ! ) when it involves a blank cell ( “” ). How can I manipulate this data including blank cells without getting #VALUE ! outputs?

Rick Rothstein · Jan 16, 2018

Salem 80 said:
Thank you very much.
The two formulas by Tetra201 and by Rick Rothstein worked lovely, with the following notes regarding the later:

The Numerical values in column A are returned to the nearest whole integer, for example a value of 0.4 we be repeated as a 0.0, a value of 1.7 will be repeated as a 2.0, as so on. I should have mentioned that the original data is not all integers and does include rational or fractional numbers.

The formula did not recognize a Zero value in Column A, in this case, the first value coming after the zero in the column will be repeated in the cells originally dedicated for the zero repetition.

Again, thank you very much, I am learning from your sharing.

I'll look at what you just posted in Message #7 shortly, but first I wanted to respond to what you posted above (from Message #6 ). None of your examples or postings indicated you could have floating point numbers in your cells, so I gave you a formula for what you did post... integers. The fix for either integers or floating point numbers is easy enough, though.... simply add a decimal point and enough zeroes to cover the largest amount of digits after the decimal point as you think you will ever have to the formula I posted in Message #2 . Below is that modification (with the addition I mentioned in red above) for an assumed maximum of 4 decimal places...

=0+(VLOOKUP("* ",TEXT(A2:A$15,"0.0000 ;;;"),1,0))

Salem 80 · Jan 17, 2018

The new formula solved the problem, I added five zeros just to be on the safe side.
Thanks.

Repetition of values in Column x times upwards

Salem 80

New Member

Excel Facts

Tetra201

MrExcel MVP

Rick Rothstein

MrExcel MVP

Tetra201

MrExcel MVP

Rick Rothstein

MrExcel MVP

Salem 80

New Member

Salem 80

New Member

Rick Rothstein

MrExcel MVP

Salem 80

New Member

Similar threads

Forum statistics

Share this page

Repetition of values in Column x times upwards

New Member

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock