Delete Numbers and Produce a List

[Paul Black]

Hi Everyone,

I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
Named "Draws".
The Minimum Number is 1, and the Maximum Number is 49 within EACH 6
Number Combination.
I have a Sheet Named "Results", and in Cell "B2" I have a Figure of the
Number of Draws I want to go Back.
If the Figure in Cell "B2" is 20 for Example, that is the Number of
Draws I want to go Back ( Starting at the Bottom and Going Up ).
I then want to Produce a List of Numbers that have NOT Been Drawn in
those 20 Draws from the 49 Numbers Please.

Many Thanks in Advance.
All the Best.
Paul

[Tom Ogilvy]

List starts in I1

Sub AA()
Dim Nums(1 To 49) As Long
Dim list() As Long, rng As Range
Dim hist As Long, cell As Range
Dim idex As Long, lastrow As Long
Dim i As Long

With Worksheets("Results")
hist = .Range("B2").Value
lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
For Each cell In rng
Nums(cell.Value) = Nums(cell.Value) + 1
Next
idex = 0
For i = 1 To 49
If Nums(i) = 0 Then
idex = idex + 1
.Cells(idex, "I").Value = i
End If
Next
End With
End Sub

--
Regards,
Tom Ogilvy

"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone,
>
> I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
> Named "Draws".
> The Minimum Number is 1, and the Maximum Number is 49 within EACH 6
> Number Combination.
> I have a Sheet Named "Results", and in Cell "B2" I have a Figure of the
> Number of Draws I want to go Back.
> If the Figure in Cell "B2" is 20 for Example, that is the Number of
> Draws I want to go Back ( Starting at the Bottom and Going Up ).
> I then want to Produce a List of Numbers that have NOT Been Drawn in
> those 20 Draws from the 49 Numbers Please.
>
> Many Thanks in Advance.
> All the Best.
> Paul
>

[Paul Black]

Thanks Very Much for the Reply Tom,

My Drawn Combinations are in the Sheet Named "Draws" ( Columns "B:G" ).
My Number of Draws to go Back are in the Sheet Named "Results" ( Cell
"B2" ).
Ideally, the List will be in the Sheet Named "Results" and Starting in
Cell "B4" and Going Down for Example.

All the Best.
Paul

Tom Ogilvy wrote:
> List starts in I1
>
> Sub AA()
> Dim Nums(1 To 49) As Long
> Dim list() As Long, rng As Range
> Dim hist As Long, cell As Range
> Dim idex As Long, lastrow As Long
> Dim i As Long
>
> With Worksheets("Results")
> hist = .Range("B2").Value
> lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
> For Each cell In rng
> Nums(cell.Value) = Nums(cell.Value) + 1
> Next
> idex = 0
> For i = 1 To 49
> If Nums(i) = 0 Then
> idex = idex + 1
> .Cells(idex, "I").Value = i
> End If
> Next
> End With
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Paul Black" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Everyone,
> >
> > I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
> > Named "Draws".
> > The Minimum Number is 1, and the Maximum Number is 49 within EACH 6
> > Number Combination.
> > I have a Sheet Named "Results", and in Cell "B2" I have a Figure of the
> > Number of Draws I want to go Back.
> > If the Figure in Cell "B2" is 20 for Example, that is the Number of
> > Draws I want to go Back ( Starting at the Bottom and Going Up ).
> > I then want to Produce a List of Numbers that have NOT Been Drawn in
> > those 20 Draws from the 49 Numbers Please.
> >
> > Many Thanks in Advance.
> > All the Best.
> > Paul
> >

[Tom Ogilvy]

Sub AA()
Dim Nums(1 To 49) As Long
Dim list() As Long, rng As Range
Dim hist As Long, cell As Range
Dim idex As Long, lastrow As Long
Dim i As Long

hist =Worksheets("Results").Range("B2").Value

With Worksheets("Draws")
lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
For Each cell In rng
Nums(cell.Value) = Nums(cell.Value) + 1
Next
idex = 3
For i = 1 To 49
If Nums(i) = 0 Then
idex = idex + 1
Worksheets("Results").Cells(idex, "B").Value = i
End If
Next
End With
End Sub

If that doesn't work, you can probably fix it.

--
Regards,
Tom Ogilvy



"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Thanks Very Much for the Reply Tom,
>
> My Drawn Combinations are in the Sheet Named "Draws" ( Columns "B:G" ).
> My Number of Draws to go Back are in the Sheet Named "Results" ( Cell
> "B2" ).
> Ideally, the List will be in the Sheet Named "Results" and Starting in
> Cell "B4" and Going Down for Example.
>
> All the Best.
> Paul
>
> Tom Ogilvy wrote:
> > List starts in I1
> >
> > Sub AA()
> > Dim Nums(1 To 49) As Long
> > Dim list() As Long, rng As Range
> > Dim hist As Long, cell As Range
> > Dim idex As Long, lastrow As Long
> > Dim i As Long
> >
> > With Worksheets("Results")
> > hist = .Range("B2").Value
> > lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> > Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
> > For Each cell In rng
> > Nums(cell.Value) = Nums(cell.Value) + 1
> > Next
> > idex = 0
> > For i = 1 To 49
> > If Nums(i) = 0 Then
> > idex = idex + 1
> > .Cells(idex, "I").Value = i
> > End If
> > Next
> > End With
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Paul Black" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Everyone,
> > >
> > > I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
> > > Named "Draws".
> > > The Minimum Number is 1, and the Maximum Number is 49 within EACH 6
> > > Number Combination.
> > > I have a Sheet Named "Results", and in Cell "B2" I have a Figure of
the
> > > Number of Draws I want to go Back.
> > > If the Figure in Cell "B2" is 20 for Example, that is the Number of
> > > Draws I want to go Back ( Starting at the Bottom and Going Up ).
> > > I then want to Produce a List of Numbers that have NOT Been Drawn in
> > > those 20 Draws from the 49 Numbers Please.
> > >
> > > Many Thanks in Advance.
> > > All the Best.
> > > Paul
> > >
>

[Paul Black]

Brilliant Tom, it Works Perfect.
One Question Please, what does the Variable "idex" do?, and Why is it
Set to "3"?.

All the Best.
Paul

Tom Ogilvy wrote:
> Sub AA()
> Dim Nums(1 To 49) As Long
> Dim list() As Long, rng As Range
> Dim hist As Long, cell As Range
> Dim idex As Long, lastrow As Long
> Dim i As Long
>
> hist =Worksheets("Results").Range("B2").Value
>
> With Worksheets("Draws")
> lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
> For Each cell In rng
> Nums(cell.Value) = Nums(cell.Value) + 1
> Next
> idex = 3
> For i = 1 To 49
> If Nums(i) = 0 Then
> idex = idex + 1
> Worksheets("Results").Cells(idex, "B").Value = i
> End If
> Next
> End With
> End Sub
>
> If that doesn't work, you can probably fix it.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Paul Black" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Very Much for the Reply Tom,
> >
> > My Drawn Combinations are in the Sheet Named "Draws" ( Columns "B:G" ).
> > My Number of Draws to go Back are in the Sheet Named "Results" ( Cell
> > "B2" ).
> > Ideally, the List will be in the Sheet Named "Results" and Starting in
> > Cell "B4" and Going Down for Example.
> >
> > All the Best.
> > Paul
> >
> > Tom Ogilvy wrote:
> > > List starts in I1
> > >
> > > Sub AA()
> > > Dim Nums(1 To 49) As Long
> > > Dim list() As Long, rng As Range
> > > Dim hist As Long, cell As Range
> > > Dim idex As Long, lastrow As Long
> > > Dim i As Long
> > >
> > > With Worksheets("Results")
> > > hist = .Range("B2").Value
> > > lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> > > Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
> > > For Each cell In rng
> > > Nums(cell.Value) = Nums(cell.Value) + 1
> > > Next
> > > idex = 0
> > > For i = 1 To 49
> > > If Nums(i) = 0 Then
> > > idex = idex + 1
> > > .Cells(idex, "I").Value = i
> > > End If
> > > Next
> > > End With
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Paul Black" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Everyone,
> > > >
> > > > I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
> > > > Named "Draws".
> > > > The Minimum Number is 1, and the Maximum Number is 49 within EACH 6
> > > > Number Combination.
> > > > I have a Sheet Named "Results", and in Cell "B2" I have a Figure of
> the
> > > > Number of Draws I want to go Back.
> > > > If the Figure in Cell "B2" is 20 for Example, that is the Number of
> > > > Draws I want to go Back ( Starting at the Bottom and Going Up ).
> > > > I then want to Produce a List of Numbers that have NOT Been Drawn in
> > > > those 20 Draws from the 49 Numbers Please.
> > > >
> > > > Many Thanks in Advance.
> > > > All the Best.
> > > > Paul
> > > >
> >

[Tom Ogilvy]

it shows which row to put the unused number in. It starts at 3 because the
first command is to add 1 to it and you wanted to write in cell B4 and down.

--
Regards,
Tom Ogilvy


"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Brilliant Tom, it Works Perfect.
> One Question Please, what does the Variable "idex" do?, and Why is it
> Set to "3"?.
>
> All the Best.
> Paul
>
> Tom Ogilvy wrote:
> > Sub AA()
> > Dim Nums(1 To 49) As Long
> > Dim list() As Long, rng As Range
> > Dim hist As Long, cell As Range
> > Dim idex As Long, lastrow As Long
> > Dim i As Long
> >
> > hist =Worksheets("Results").Range("B2").Value
> >
> > With Worksheets("Draws")
> > lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> > Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist, 6)
> > For Each cell In rng
> > Nums(cell.Value) = Nums(cell.Value) + 1
> > Next
> > idex = 3
> > For i = 1 To 49
> > If Nums(i) = 0 Then
> > idex = idex + 1
> > Worksheets("Results").Cells(idex, "B").Value = i
> > End If
> > Next
> > End With
> > End Sub
> >
> > If that doesn't work, you can probably fix it.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Paul Black" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Very Much for the Reply Tom,
> > >
> > > My Drawn Combinations are in the Sheet Named "Draws" ( Columns
"B:G" ).
> > > My Number of Draws to go Back are in the Sheet Named "Results" ( Cell
> > > "B2" ).
> > > Ideally, the List will be in the Sheet Named "Results" and Starting in
> > > Cell "B4" and Going Down for Example.
> > >
> > > All the Best.
> > > Paul
> > >
> > > Tom Ogilvy wrote:
> > > > List starts in I1
> > > >
> > > > Sub AA()
> > > > Dim Nums(1 To 49) As Long
> > > > Dim list() As Long, rng As Range
> > > > Dim hist As Long, cell As Range
> > > > Dim idex As Long, lastrow As Long
> > > > Dim i As Long
> > > >
> > > > With Worksheets("Results")
> > > > hist = .Range("B2").Value
> > > > lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
> > > > Set rng = .Cells(lastrow, 2).Offset(-1 * (hist - 1), 0).Resize(hist,
6)
> > > > For Each cell In rng
> > > > Nums(cell.Value) = Nums(cell.Value) + 1
> > > > Next
> > > > idex = 0
> > > > For i = 1 To 49
> > > > If Nums(i) = 0 Then
> > > > idex = idex + 1
> > > > .Cells(idex, "I").Value = i
> > > > End If
> > > > Next
> > > > End With
> > > > End Sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Paul Black" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Everyone,
> > > > >
> > > > > I have a List of 6 Number Combinations in Columns "B:G" in a Sheet
> > > > > Named "Draws".
> > > > > The Minimum Number is 1, and the Maximum Number is 49 within EACH
6
> > > > > Number Combination.
> > > > > I have a Sheet Named "Results", and in Cell "B2" I have a Figure
of
> > the
> > > > > Number of Draws I want to go Back.
> > > > > If the Figure in Cell "B2" is 20 for Example, that is the Number
of
> > > > > Draws I want to go Back ( Starting at the Bottom and Going Up ).
> > > > > I then want to Produce a List of Numbers that have NOT Been Drawn
in
> > > > > those 20 Draws from the 49 Numbers Please.
> > > > >
> > > > > Many Thanks in Advance.
> > > > > All the Best.
> > > > > Paul
> > > > >
> > >
>

[Paul Black]

Brilliant Tom.

Thanks for ALL your Help, it is Appreciated.
All the Best.
Paul