I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row. If
column B is "B", repeat data 3 times in a row, and so forth. Please help with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

You can do what you asked with this macro...

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To 69 - Asc(.Cells(X, 2).Value)
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C1").Sort Key1:=.Columns("C")
End With
End Sub

It wasn't clear to me what criteria you used to sort Column C... it's either
alphabetical or by the repeat count... I assumed alphabetical in the above
routine.

Rick


"EK" wrote in message
...
I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row.
If
column B is "B", repeat data 3 times in a row, and so forth. Please help
with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

First sort your data by column B and then run:

Sub temp()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For nn = 1 To n
nt = 69 - Asc(Cells(nn, 2).Value)
v = Cells(nn, 1).Value
For times = 1 To nt
Cells(k, 3).Value = v
k = k + 1
Next
Next
End Sub

--
Gary''s Student - gsnu200750


"EK" wrote:

I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row. If
column B is "B", repeat data 3 times in a row, and so forth. Please help with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

Sorry, I'm afraid I do not know macro. Do I have to substitute the X, Y,
LastRow, etc. with specific numbers? What do they correspond to? -EK

"Rick Rothstein (MVP - VB)" wrote:

You can do what you asked with this macro...

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To 69 - Asc(.Cells(X, 2).Value)
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C1").Sort Key1:=.Columns("C")
End With
End Sub

It wasn't clear to me what criteria you used to sort Column C... it's either
alphabetical or by the repeat count... I assumed alphabetical in the above
routine.

Rick


"EK" wrote in message
...
I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row.
If
column B is "B", repeat data 3 times in a row, and so forth. Please help
with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

I think I got the answer from this macro. Appreciate all who tried to the
help. Thanks!

"Gary''s Student" wrote:

First sort your data by column B and then run:

Sub temp()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For nn = 1 To n
nt = 69 - Asc(Cells(nn, 2).Value)
v = Cells(nn, 1).Value
For times = 1 To nt
Cells(k, 3).Value = v
k = k + 1
Next
Next
End Sub

--
Gary''s Student - gsnu200750


"EK" wrote:

I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row. If
column B is "B", repeat data 3 times in a row, and so forth. Please help with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

I've made a minor modification to my subroutine, so when you follow the
instructions below, make sure you use the code posted in this message.

Go to the worksheet with your data on it, right-click the tab for this sheet
and select View Code from the popup menu that appears. This process will
have taken you into the VBA editor and opened the code window for the
worksheet you were just on. The next thing for you to do is Copy the code
below my signature and Paste it into the opened code window in the VBA
editor. You can now close the VBA editor and return to your worksheet. To
use this macro from the worksheet, simple press Alt+F8 and Select/Run the
CreateReports macro from the list.

Rick

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With ActiveSheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To 69 - Asc(.Cells(X, 2).Value)
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C1").Sort Key1:=.Columns("C")
End With
End Sub

One other thing. From this macro, what do I need to change in order to
re-populate with a different definition such as:
A=6 times, B=3 times, C=2 times and D=1 time.

EK
"EK" wrote:

I think I got the answer from this macro. Appreciate all who tried to the
help. Thanks!

"Gary''s Student" wrote:

First sort your data by column B and then run:

Sub temp()
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For nn = 1 To n
nt = 69 - Asc(Cells(nn, 2).Value)
v = Cells(nn, 1).Value
For times = 1 To nt
Cells(k, 3).Value = v
k = k + 1
Next
Next
End Sub

--
Gary''s Student - gsnu200750


"EK" wrote:

I have following data:

A B
-----------------
Apples A
Pears D
Orange C
Grapes B

If column B is "A", repeat corresponding column A data 4 times in a row. If
column B is "B", repeat data 3 times in a row, and so forth. Please help with
solution which will result in the following:

column C
----------
Apples
Apples
Apples
Apples
Grapes
Grapes
Grapes
Orange
Orange
Pears

Yes, it works. Except that this code also re-sorted the order of the data in
Col A and B. I would like it sorted Col C by the repeat count and leave Col A
and B intact. The code from Gary"s Student did just that.

For this code, what do I have to change in order to re-populate with a
different definition such as:
A=6 times, B=3 times, C=2 times and D=1 time?

Thanks, EK.




"Rick Rothstein (MVP - VB)" wrote:

I've made a minor modification to my subroutine, so when you follow the
instructions below, make sure you use the code posted in this message.

Go to the worksheet with your data on it, right-click the tab for this sheet
and select View Code from the popup menu that appears. This process will
have taken you into the VBA editor and opened the code window for the
worksheet you were just on. The next thing for you to do is Copy the code
below my signature and Paste it into the opened code window in the VBA
editor. You can now close the VBA editor and return to your worksheet. To
use this macro from the worksheet, simple press Alt+F8 and Select/Run the
CreateReports macro from the list.

Rick

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With ActiveSheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To 69 - Asc(.Cells(X, 2).Value)
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C1").Sort Key1:=.Columns("C")
End With
End Sub

Yes, it works. Except that this code also re-sorted the order of the data
in
Col A and B. I would like it sorted Col C by the repeat count and leave
Col A
and B intact. The code from Gary"s Student did just that.

Fixed in the code below.

For this code, what do I have to change in order to re-populate with a
different definition such as:
A=6 times, B=3 times, C=2 times and D=1 time?

You didn't say you would need other encodings, so I (and Gary) created an
algorithm to do exactly what you asked. To make it changeable will require a
different algorithmic approach. Assuming you will need these repeat values
to be changeable, I think the best way to handle it would be to store the
repeat values in cells on the spreadsheet (you can hide the Column so they
are not seen if you wish). For the (new) subroutine code below, I am
assuming the A repeat value will be stored in F1, the B repeat value in F2,
the C repeat value in F3 and the D repeat value in F4.

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With ActiveSheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To Range("F" & CStr(Asc(.Cells(X, 2).Value) - 64)).Value
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C:C").Sort Key1:=.Columns("C")
End With
End Sub


Rick

Rick and Gary,
I apologise for not being specific earlier. Thank you for the new code.

EK

"Rick Rothstein (MVP - VB)" wrote:

Yes, it works. Except that this code also re-sorted the order of the data
in
Col A and B. I would like it sorted Col C by the repeat count and leave
Col A
and B intact. The code from Gary"s Student did just that.

Fixed in the code below.

For this code, what do I have to change in order to re-populate with a
different definition such as:
A=6 times, B=3 times, C=2 times and D=1 time?

You didn't say you would need other encodings, so I (and Gary) created an
algorithm to do exactly what you asked. To make it changeable will require a
different algorithmic approach. Assuming you will need these repeat values
to be changeable, I think the best way to handle it would be to store the
repeat values in cells on the spreadsheet (you can hide the Column so they
are not seen if you wish). For the (new) subroutine code below, I am
assuming the A repeat value will be stored in F1, the B repeat value in F2,
the C repeat value in F3 and the D repeat value in F4.

Sub CreateRepeats()
Dim X As Long
Dim Y As Long
Dim LastRow As Long
Dim LastDataRow As Long
LastDataRow = 1
With ActiveSheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For X = 1 To LastRow
For Y = 1 To Range("F" & CStr(Asc(.Cells(X, 2).Value) - 64)).Value
Cells(LastDataRow, 3).Value = Cells(X, 1)
LastDataRow = LastDataRow + 1
Next
Next
.Range("C:C").Sort Key1:=.Columns("C")
End With
End Sub


Rick