Repeat values in column A a certain number of times depending on thevalue in column B

  • Thread starter Thread starter Harry Flashman
  • Start date Start date
H

Harry Flashman

Imagine a set of data as set out below:
Column A Column B
Apples 24
Pears 36
Oranges 8

I want to poplulate a column (for exampel column A on a new sheet),
where the values in column A will be repeated as many times as the
value in column B
Thus the first 24 rows will say Apples, the next 36 rows will Pears,
Oranges.
I need a formula that recogonizes that when it gets to row 25 that it
should no longer need to copy Apples, but then switch to pears.

This might sound like an unusual request but if I can grasp a way to
do this I can create the table I need and populate the rest of the
data using lookup funcitons.

Would anybody be able to help me please?
 
Hi,

Here's some code that will do what you are asking.

Sub myRepeat()
Dim cell As Range
Dim I As Integer
I = 0
For Each cell In Selection
Range(Sheets("Sheet2").Range("D1").Offset(I, 0),
Sheets("Sheet2").Range("D1").Offset(I + cell - 1)) _
= cell.Offset(0, -1)
I = I + cell
Next cell
End Sub

In this case you run it from Sheet1 and you select the range with the count,
say B1:B3 first.
 
Hi,

Here's some code that will do what you are asking.

Sub myRepeat()
    Dim cell As Range
    Dim I As Integer
    I = 0
    For Each cell In Selection
        Range(Sheets("Sheet2").Range("D1").Offset(I, 0),
Sheets("Sheet2").Range("D1").Offset(I + cell - 1)) _
        = cell.Offset(0, -1)
        I = I + cell
    Next cell
End Sub

In this case you run it from Sheet1 and you select the range with the count,
say B1:B3 first.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire








- Show quoted text -
Click to expand...

One way without using VB would be to use a extra column to calculate
the cumalative row value.

COL A COL B COL C
Apples 26 =B8
Pears 6 =C8+B8
Oranges 6 =B9+C9

in column D I have the formula

=IF(ROW()>SUM(B:B),"",LOOKUP(ROW()+$C$1-1,$C$1:$C$3,$A$1:$A$3))

copy this formula down in COL C as far as you need to

HTH

Regards

David
 
One way without using VB would be to use a extra column to calculate
the cumalative row value.

COL A   COL B   COL C
Apples  26      =B8
Pears   6       =C8+B8
Oranges 6       =B9+C9

in column D I have the formula

=IF(ROW()>SUM(B:B),"",LOOKUP(ROW()+$C$1-1,$C$1:$C$3,$A$1:$A$3))

copy this formula down in COL C as far as you need to

HTH

Regards

David- Hide quoted text -

- Show quoted text -
Click to expand...

David, I tweaked your foruma very slightly so that I would not get a
circular reference error
This worked perfectly
=IF(ROW()>SUM(B:B),"",LOOKUP(ROW()+$B$1-1,$B$1:$B$3,$A$1:$A$3))
I was trying to figure out a solution like that myself but I couldn't
quite work it out. This is great. Both the formula and the macro will
come in handy. Thanks very much.
 
Hi,

Here's some code that will do what you are asking.

Sub myRepeat()
    Dim cell As Range
    Dim I As Integer
    I = 0
    For Each cell In Selection
        Range(Sheets("Sheet2").Range("D1").Offset(I, 0),
Sheets("Sheet2").Range("D1").Offset(I + cell - 1)) _
        = cell.Offset(0, -1)
        I = I + cell
    Next cell
End Sub

In this case you run it from Sheet1 and you select the range with the count,
say B1:B3 first.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire








- Show quoted text -
Click to expand...

I was looking for a formula solution but that macro actually works
very well thank you.
 
I am looking to use this method to solve a problem at work, but I don't have the "3DIF" function in Excel. Is it an add-on, UDF, or only available in a previous version or what. I'm using Excel 2007 version 12.0.6504.5001 with SP1 MSO 12.0.6320.5000. Thanks in advance for your help.

-Dan



Posted as a reply to:

Re: Repeat values in column A a certain number of times depending on t

On Jul 9, 12:39=A0pm, Shane Devenshir
nt

One way without using VB would be to use a extra column to calculat
the cumalative row value

COL A COL B COL
Apples 26 =3DB
Pears 6 =3DC8+B
Oranges 6 =3DB9+C

in column D I have the formul

=3DIF(ROW()>SUM(B:B),"",LOOKUP(ROW()+$C$1-1,$C$1:$C$3,$A$1:$A$3)

copy this formula down in COL C as far as you need t

HT

Regard

David

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx
 
I am looking to use this method to solve a problem at work, but I don't have the "3DIF" function in Excel. Is it an add-on, UDF, or only available in a previous version or what. I'm using Excel 2007 version 12.0.6504.5001 with SP1 MSO 12.0.6320.5000. Thanks in advance for your help.

-Dan
Click to expand...

It is either an add-on or a UDF. Or possibly a NAME'd formula.

Perhaps if you search the thread in which the message from Shane was posted,
you may find enlightenment.
--ron
 
I think you will find that the 3D was added somewhere in transit. I used to
have this problem years ago. Just remove the 3D part

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
in message news:[email protected]...
 
Hello All,

I believe this solution would work perfectly for a random name generator I am creating. This would populate names based on frequency used. I cannot seem to get either of the above formula based solutions to work. I believe I am placing the formulas in the wrong columns/rows/cells. Any advice or an example in an actual excel spreadsheet would be greatly appreciated!

Thanks

Steve
 
Back
Top