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