| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Numbers and Produce a List
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
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" wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
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" wrote in message oups.com... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
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" wrote in message ups.com... 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" wrote in message oups.com... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
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" wrote in message ups.com... 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" wrote in message oups.com... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
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" wrote in message oups.com... 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" wrote in message ups.com... 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" wrote in message oups.com... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Numbers and Produce a List
Brilliant Tom.
Thanks for ALL your Help, it is Appreciated. All the Best. Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| produce list automatically from choice of drop down list | Excel Discussion (Misc queries) | |||
| How do I delete unique numbers from a list and save duplicates? | Excel Worksheet Functions | |||
| How do I produce Automatic Quotation Numbers in Excel? | Excel Discussion (Misc queries) | |||
| produce a formulate to produce assigned seats for dinner | Excel Worksheet Functions | |||
| How do I delete a character from a list of item numbers? | Excel Worksheet Functions | |||
Linear Mode
