I'm looking for a macro that makes a single long list of data from 2 user
input columns of data (column A are the data values themselves & column B are
the frequencies of those values). Example - Col. A contains 1, 2, 3, and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2 user
input columns of data (column A are the data values themselves & column B are
the frequencies of those values). Example - Col. A contains 1, 2, 3, and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What you've
managed to do is to list in each cell all of the data values corresponding to
it's frequency. In my example you have 3 cells (1,1,1,1,1), (2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20 cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2 user
input columns of data (column A are the data values themselves & column B are
the frequencies of those values). Example - Col. A contains 1, 2, 3, and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20 cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves & column
B are
the frequencies of those values). Example - Col. A contains 1, 2, 3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Good morning Tom,

Sorry, but still no success. I believe there are 2 problems now:
1. When I copy & paste your macro into the Visual Basic Editor, one line of
code appears in red. It appears to me that there is a missing right
parenthesis ). I know enough to count both left & right parentheses, and the
2 counts are not equal. If this is a problem, where does the missing right
parenthesis go?

2. Having put the missing right parenthesis first before the < sign, giving
4 left & 4 right parentheses, then running your macro with my selection in
column A gives an error entitled "Run-time error '424':
Object required
Debugging the error highlights the line of code Set c1 = cell.Offset(0, 1)
in yellow.

So I moved the missing right parenthesis to before the word then, giving 7
left & 7 right parentheses, and I get exactly the same error message.

Since I have no idea as to what this error message means, I would appreciate
your assistance.

My Excel spreadsheet is quite simply in cell A1 is the label Data, in B1 is
Freq, and in C1 is List. You already know my data and corresponding
frequencies.

Thanks,
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20 cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves & column
B are
the frequencies of those values). Example - Col. A contains 1, 2, 3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Sub ABC()
Dim rng As Range, c As Range, c1 As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long, k As Long

Set c = Range("A1")
Set c1 = Range("B1")

s = c1.Value
s1 = c1.Value
If (Len(c) - Len(Replace(c, ",", ""))) < (Len(c1) _
- Len(Replace(c1, ",", ""))) Then
MsgBox "required data not present"
Exit Sub
End If
v = Split(s, ",")
v1 = Split(s1, ",")
k = 0
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.Offset(k, 2).Value = v(i)
k = k + 1
Next j
Next i
End Sub

--
regards,
Tom Ogilvy


"BWoods" wrote in message
...
Good morning Tom,

Sorry, but still no success. I believe there are 2 problems now:
1. When I copy & paste your macro into the Visual Basic Editor, one line
of
code appears in red. It appears to me that there is a missing right
parenthesis ). I know enough to count both left & right parentheses, and
the
2 counts are not equal. If this is a problem, where does the missing
right
parenthesis go?

2. Having put the missing right parenthesis first before the < sign,
giving
4 left & 4 right parentheses, then running your macro with my selection in
column A gives an error entitled "Run-time error '424':
Object required
Debugging the error highlights the line of code Set c1 = cell.Offset(0, 1)
in yellow.

So I moved the missing right parenthesis to before the word then, giving 7
left & 7 right parentheses, and I get exactly the same error message.

Since I have no idea as to what this error message means, I would
appreciate
your assistance.

My Excel spreadsheet is quite simply in cell A1 is the label Data, in B1
is
Freq, and in C1 is List. You already know my data and corresponding
frequencies.

Thanks,
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values
corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20
cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves &
column
B are
the frequencies of those values). Example - Col. A contains 1, 2,
3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values
that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's.
I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Sorry Tom, but this didn't work either. As I explained my setup to you
earlier, in cell A1 is the word Data, cell B1 is Freq and cell C1 is List.
When I run your macro the new error message I get is:
Run-time error '13':
Type mismatch

Clicking on debug highlights line 21 in yellow that reads
Fot j = 1 To v1(i)

So I decided to try your macro by deleting my first row of labels. That is,
I now have only numbers in my spreadsheet. The macro still doesn't work.
What the macro does is it lists in column C the value from cell B1 that
number of times. In my example, your macro puts the value 5 in C1, C2, C3,
C4, and C5. That's all.

Since I'm trying to understand how your macro works, I changed my cell B1
value to 7 then ran your macro and it put the value 7 in the 7 cells C1, C2,
C3, C4, C5, C6, & C7.

I hope I'm not taking too much of your time in working on my project. I
appreciate all of your time & effort.

Thank you,
Barry

"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long, k As Long

Set c = Range("A1")
Set c1 = Range("B1")

s = c1.Value
s1 = c1.Value
If (Len(c) - Len(Replace(c, ",", ""))) < (Len(c1) _
- Len(Replace(c1, ",", ""))) Then
MsgBox "required data not present"
Exit Sub
End If
v = Split(s, ",")
v1 = Split(s1, ",")
k = 0
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.Offset(k, 2).Value = v(i)
k = k + 1
Next j
Next i
End Sub

--
regards,
Tom Ogilvy


"BWoods" wrote in message
...
Good morning Tom,

Sorry, but still no success. I believe there are 2 problems now:
1. When I copy & paste your macro into the Visual Basic Editor, one line
of
code appears in red. It appears to me that there is a missing right
parenthesis ). I know enough to count both left & right parentheses, and
the
2 counts are not equal. If this is a problem, where does the missing
right
parenthesis go?

2. Having put the missing right parenthesis first before the < sign,
giving
4 left & 4 right parentheses, then running your macro with my selection in
column A gives an error entitled "Run-time error '424':
Object required
Debugging the error highlights the line of code Set c1 = cell.Offset(0, 1)
in yellow.

So I moved the missing right parenthesis to before the word then, giving 7
left & 7 right parentheses, and I get exactly the same error message.

Since I have no idea as to what this error message means, I would
appreciate
your assistance.

My Excel spreadsheet is quite simply in cell A1 is the label Data, in B1
is
Freq, and in C1 is List. You already know my data and corresponding
frequencies.

Thanks,
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values
corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20
cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves &
column
B are
the frequencies of those values). Example - Col. A contains 1, 2,
3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values
that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's.
I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Hello Tom,

A gentleman by the name of StephenR wrote the following marco for my
specific example & it worked.

Sub xx()

Dim r1, r2, r3 As Integer

With Worksheets("Sheet1")

r1 = .Range("B1").Value
r2 = r1 + .Range("B2").Value
r3 = r2 + .Range("B3").Value

.Range(Cells(1, 3), Cells(r1, 3)) = .Range("A1").Value
.Range(Cells(r1 + 1, 3), Cells(r2, 3)) = .Range("A2").Value
.Range(Cells(r2 + 1, 3), Cells(r3, 3)) = .Range("A3").Value

End With

End Sub

Since I still don't know what all this means, does it help you to generate a
macro that will list all of my data from 2 user input columns of data?

Thanks,
Barry

"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long, k As Long

Set c = Range("A1")
Set c1 = Range("B1")

s = c1.Value
s1 = c1.Value
If (Len(c) - Len(Replace(c, ",", ""))) < (Len(c1) _
- Len(Replace(c1, ",", ""))) Then
MsgBox "required data not present"
Exit Sub
End If
v = Split(s, ",")
v1 = Split(s1, ",")
k = 0
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.Offset(k, 2).Value = v(i)
k = k + 1
Next j
Next i
End Sub

--
regards,
Tom Ogilvy


"BWoods" wrote in message
...
Good morning Tom,

Sorry, but still no success. I believe there are 2 problems now:
1. When I copy & paste your macro into the Visual Basic Editor, one line
of
code appears in red. It appears to me that there is a missing right
parenthesis ). I know enough to count both left & right parentheses, and
the
2 counts are not equal. If this is a problem, where does the missing
right
parenthesis go?

2. Having put the missing right parenthesis first before the < sign,
giving
4 left & 4 right parentheses, then running your macro with my selection in
column A gives an error entitled "Run-time error '424':
Object required
Debugging the error highlights the line of code Set c1 = cell.Offset(0, 1)
in yellow.

So I moved the missing right parenthesis to before the word then, giving 7
left & 7 right parentheses, and I get exactly the same error message.

Since I have no idea as to what this error message means, I would
appreciate
your assistance.

My Excel spreadsheet is quite simply in cell A1 is the label Data, in B1
is
Freq, and in C1 is List. You already know my data and corresponding
frequencies.

Thanks,
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values
corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20
cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves &
column
B are
the frequencies of those values). Example - Col. A contains 1, 2,
3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values
that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's.
I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks

Tom,

Thanks to Dave Hawley in Australia, here's his macro:

Sub TryMe()
Dim rRange As Range, rCell As Range
Dim lNum As Long

Set rRange = Range("B1", Range("B" & Rows.Count).End(xlUp))


For Each rCell In rRange
If rCell.Row = 1 And rCell 1 Then
rCell(1, 2).Range("A1:A" & rCell) = rCell.Offset(0, -1)
ElseIf rCell = 1 And rCell.Row = 1 Then
rCell(1, 2) = rCell.Offset(0, -1)
ElseIf rCell = 1 Then
Cells(Rows.Count, 3).End(xlUp)(2, 1) = rCell.Offset(0, -1)
Else
Cells(Rows.Count, 3).End(xlUp)(2, 1).Range("A1:A" & rCell) =
rCell.Offset(0, -1)
End If
Next rCell
End Sub

Thanks for your help,
Barry

"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long, k As Long

Set c = Range("A1")
Set c1 = Range("B1")

s = c1.Value
s1 = c1.Value
If (Len(c) - Len(Replace(c, ",", ""))) < (Len(c1) _
- Len(Replace(c1, ",", ""))) Then
MsgBox "required data not present"
Exit Sub
End If
v = Split(s, ",")
v1 = Split(s1, ",")
k = 0
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.Offset(k, 2).Value = v(i)
k = k + 1
Next j
Next i
End Sub

--
regards,
Tom Ogilvy


"BWoods" wrote in message
...
Good morning Tom,

Sorry, but still no success. I believe there are 2 problems now:
1. When I copy & paste your macro into the Visual Basic Editor, one line
of
code appears in red. It appears to me that there is a missing right
parenthesis ). I know enough to count both left & right parentheses, and
the
2 counts are not equal. If this is a problem, where does the missing
right
parenthesis go?

2. Having put the missing right parenthesis first before the < sign,
giving
4 left & 4 right parentheses, then running your macro with my selection in
column A gives an error entitled "Run-time error '424':
Object required
Debugging the error highlights the line of code Set c1 = cell.Offset(0, 1)
in yellow.

So I moved the missing right parenthesis to before the word then, giving 7
left & 7 right parentheses, and I get exactly the same error message.

Since I have no idea as to what this error message means, I would
appreciate
your assistance.

My Excel spreadsheet is quite simply in cell A1 is the label Data, in B1
is
Freq, and in C1 is List. You already know my data and corresponding
frequencies.

Thanks,
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, c As Range, c1 as Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long

set c = ActiveCell
if c.column < 1 then
msgbox "Selection must be in column A"
exit sub
End if
s = c.Value
set c1 = cell.offset(0,1)
s1 = c1.Value
if (len(c)-len(replace(c,",","")) < (len(c1)-replace(c1,",","")) then
msgbox "required data not present"
exit sub
End if
v = Split(s, ",")
v1 = Split(s1, ",")
For i = LBound(v) To UBound(v)
For j = 1 To v1(i)
c.offset(j-1,2).Value = v(i)
Next j
Next i
End Sub

--
Regards,
Tom Ogilvy


"BWoods" wrote in message
...
Hi Tom,

Thank you for your quick reply, but we're not quite there yet. What
you've
managed to do is to list in each cell all of the data values
corresponding
to
it's frequency. In my example you have 3 cells (1,1,1,1,1),
(2,2,2,2,2,2),
and (3,3,3,3,3,3,3,3,3). What I'm looking for is a long list of 20
cells
each with one individual value in it such as:
Column C
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3

Thank you for your help.
Barry


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range, cell As Range
Dim v As Variant, v1 As Variant
Dim s As String, s1 As String
Dim i As Long, j As Long
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
For Each cell In rng
v = Split(cell, ",")
v1 = Split(cell.Offset(0, 1), ",")
s1 = ""
For i = LBound(v) To UBound(v)
s = ""
For j = 1 To v1(i)
s = s & v(i) & ","
Next j
s1 = s1 & s
Next i
cell.Offset(0, 2).Value = Left(s1, Len(s1) - 1)
Next cell
End Sub

Obviously test on a copy of your data.

--
Regards,
Tom Ogilvy



"BWoods" wrote:

I'm looking for a macro that makes a single long list of data from 2
user
input columns of data (column A are the data values themselves &
column
B are
the frequencies of those values). Example - Col. A contains 1, 2,
3,
and
Col. B contains 5, 6, 9. This is actually a data set of 20 values
that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's.
I'd
like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
Thanks