Repeat value in column A by number in column B in column C

[sportjunkie42]

I'm looking for an easy way to repeat a value in column A by a number in column B and put those results in column C.

Before:

Josh 3
Lee 1
John 2

After:
Josh
Josh
Josh
Lee
John
John

Amy help would be greatly appreciated.

 

[isabelle]

hi,

Sub Macro1()
Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")

For i = 1 To wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row
With wks2
LastCell = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
wks1.Cells(i, 1).Copy .Range(.Cells(LastCell, 1), .Cells(LastCell +
wks1.Cells(i, 2) - 1, 1))
Application.CutCopyMode = False
End With
Next
End Sub

isabelle

Le 2013-08-16 18:39, (e-mail address removed) a écrit :

 

[isabelle]

oops : put those results in column C

Sub Macro1()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
LastCell = Cells(Cells.Rows.Count, 3).End(xlUp).Row + 1
Cells(i, 1).Copy Range(Cells(LastCell, 3), Cells(LastCell + Cells(i,
2) - 1, 3))
Application.CutCopyMode = False
Next
Range("C1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

isabelle

 

[isabelle]

another way without using copy

Sub Macro2()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
LastCell = Cells(Cells.Rows.Count, 3).End(xlUp).Row + 1
Range(Cells(LastCell, 3), Cells(LastCell + Cells(i, 2) - 1, 3)).Value
= Cells(i, 1)
Next
Range("C1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

isabelle

 

[Ron Rosenfeld]

I'm looking for an easy way to repeat a value in column A by a number in column B and put those results in column C.

Before:

Josh 3
Lee 1
John 2

After:
Josh
Josh
Josh
Lee
John
John

Amy help would be greatly appreciated.

Here's a method using VBA arrays:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
==========================
Option Explicit
Sub RptA()
Dim rSrc As Range
Dim vSrc As Variant
Dim vRes() As Variant
Dim rDest As Range
Dim i As Long, j As Long
Set rDest = Range("C1")
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)
vSrc = rSrc

j = 1
ReDim vRes(1 To WorksheetFunction.Sum(rSrc.Columns(2)), 1 To 2)
For i = 1 To UBound(vSrc)
For j = j To j + vSrc(i, 2) - 1
vRes(j, 1) = vSrc(i, 1)
Next j
Next i

Set rDest = rDest.Resize(rowsize:=UBound(vRes))
rDest = vRes
End Sub
==================================

 

[isabelle]

ho yes much better,
thank you Ron for this code,

isabelle

Le 2013-08-16 22:51, Ron Rosenfeld a écrit :

 

[isabelle]

hi Ron,

this is the first time i see a Redim with 2 dimentions
and i trying to understand, so i changed
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 2)
by
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 1)
and everything works well, please could you explain to me why 2 dimentions?

isabelle

Le 2013-08-16 22:51, Ron Rosenfeld a écrit :

 

[isabelle]

i realize that my question may bring to confusion, my question is about
you added a second argument

Le 2013-08-17 00:40, isabelle a écrit :

 

[GS]

this is the first time i see a Redim with 2 dimentions

and i trying to understand, so i changed
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 2)
by
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 1)
and everything works well, please could you explain to me why 2 dimentions?

Hi Isabelle,
I don't mean to butt in here but maybe you'll find the following
helpful...

A worksheet is a 2D array of cells; Dim1 = num rows, dim2=num cols.
This is by default what you get when you 'dump' a range into a Variant,
and so vSrc(1 To RowCount, 1 To ColCount) is the resulting size of the
array.

Row/col indexes start at 1 and so vSrc(1, 1) contains the value of
row1,col1. vSrc(1, 2) contains the value of row1, col2.

Redim vRes(1 To 6, 1 To 2) results a 6 row by 2 col 2D array that can
be 'dumped' back into the worksheet without the need to Transpose. The
Transpose function has limitations that, though well beyond this
scenario, avoiding the need of it is a good idea when the array needs
to go back into a sheet.

Ron's loop returns a 2-col array where the 2nd col is blank. Your Redim
to 1 col has the same effect except the OP wanted no values in col2 and
Ron's use of '1 To 2' for the 2nd dim clears the values in col2 when
the array is assigned back to A1:B6.

HTH

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

hi Ron,

this is the first time i see a Redim with 2 dimentions
and i trying to understand, so i changed
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 2)
by
ReDim lips (1TB WorksheetFunction.Sum (rSrc.Columns (2)), 1 To 1)
and everything works well, please could you explain to me why 2 dimentions?

isabelle

There is a slight typo (that has no effect on the result). It really should have been:

ReDim vRes(1 To WorksheetFunction.Sum(rSrc.Columns(2)), 1 To 1)

The reason for two dimensions is that a single-dimensioned VBA array is a "horizontal" array, so would have to be transposed before assigning to a vertical range (or one could loop through and assign cells one by one, but that loses the efficiency). Also, there is a limit on how large an array one can transpose (I think it might be the number of columns), with large data sets one can run into that limit.

A 2-dimensioned array is multicolumns, where the first dimension represents the row, and the second the column. You can see this more clearly by examining the array that is produced when you assign a range to a VBA array.

The reason my error (1 to 2 v. 1 to 1) makes no difference is that the range to which I am writing is only a single column, so the second column in the array is ignored.

One reason I like the array method is that it avoids multiple calls to and form the worksheet, thereby speeding up things considerably. So I try to use it when I can.

 

[Ron Rosenfeld]

Ron's loop returns a 2-col array where the 2nd col is blank. Your Redim
to 1 col has the same effect except the OP wanted no values in col2 and
Ron's use of '1 To 2' for the 2nd dim clears the values in col2 when
the array is assigned back to A1:B6.

Actually, the 2-col array was a mistake, and it should have been a 1-col array. The results are getting dumped to col c.
The error had no apparent effect because the range to which the array was being assigned was a 1-col array, so the 2nd column was ignored.

 

[Ron Rosenfeld]

I'm looking for an easy way to repeat a value in column A by a number in column B and put those results in column C.

Before:

Josh 3
Lee 1
John 2

After:
Josh
Josh
Josh
Lee
John
John

Amy help would be greatly appreciated.

Here is a better version of my original submission. It corrects a minor error (that had no effect on the process) -- see my comments to Isabelle and GS, and also clears column C before writing the results of the repetitions.
Also, by turning off Screen Updating, it further speeds execution time.

==============================
Option Explicit
Sub RptA()
Dim rSrc As Range
Dim vSrc As Variant
Dim vRes() As Variant
Dim rDest As Range
Dim i As Long, j As Long
Set rDest = Range("C1")
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=2)
vSrc = rSrc

j = 1
ReDim vRes(1 To WorksheetFunction.Sum(rSrc.Columns(2)), 1 To 1)
For i = 1 To UBound(vSrc)
For j = j To j + vSrc(i, 2) - 1
vRes(j, 1) = vSrc(i, 1)
Next j
Next i

Set rDest = rDest.Resize(rowsize:=UBound(vRes))

Application.ScreenUpdating = False
rDest.EntireColumn.ClearContents
rDest = vRes
Application.ScreenUpdating = True

End Sub
======================================

 

[GS]

Actually, the 2-col array was a mistake, and it should have been a 1-col
array. The results are getting dumped to col c. The error had no apparent
effect because the range to which the array was being assigned was a 1-col
array, so the 2nd column was ignored.

Yes, after rereading the OP I see my err! Thanks for pointing out that
the destination col is C (how did I miss that?), and so 1 To 1 should
indeed be the 2nd dim. My explanation is only good if the original data
is to be replaced!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

ok thank you Ron and Garry for those explanations, now i understand that
the two dimentions are necessary to tranpose on the sheet

isabelle

Le 2013-08-17 07:30, Ron Rosenfeld a écrit :

Here is a better version of my original submission. It corrects a minor error (that had no effect on the process)

-- see my comments to Isabelle and GS, and also clears column C before
writing the results of the repetitions.

 

[sportjunkie42]

Thanks Ron, Isabelle, and everyone else that chimed in! This is going to save me tons of time!!!