Help on a task
 

Hi Gang:

Need some help on a query I have a long list of 3000 numbers. like
this

91290007957
91290007977
91290008257
91400000000
92400000000
01500000000
91401039801
90550000000
00160000000
91401050593

I want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000

then of those that are left I would sort off any that the 4th number
was not an 8 or 9

90550000000
00160000000

Sincerely,


thanks in advance


Dave

Help on a task
 

Two questions:

First - are those numbers stored as real numbers and the cells formatted to
show leading zeros, OR are they actually text entries (cells either formatted
as TEXT or numbers preceded with an apostrophe ['])?

Second - what do you mean by "sort off" any numbers. Do you mean remove
them from the list, or move them to a separate list? I think you mean to
delete them from the list, but I'm not certain.

"Dave" wrote:

Hi Gang:

Need some help on a query I have a long list of 3000 numbers. like
this

91290007957
91290007977
91290008257
91400000000
92400000000
01500000000
91401039801
90550000000
00160000000
91401050593

I want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000

then of those that are left I would sort off any that the 4th number
was not an 8 or 9

90550000000
00160000000

Sincerely,


thanks in advance


Dave





.

Help on a task
 

I have assumed that the entries are text for this solution. And either I've
got it right, or I have it 100% bass-akwards in the results. But if that's
the case, then some simple logic changes to the code will get it right.

MAKE A COPY of your workbook. We don't want to mess up your list of numbers
just yet. In theory, this code should leave the original list untouched, but
you know the old adage about best laid plans and all, so work from a copy
initially.

Here's code. You'll need to edit some values in it to identify the column
with the numbers in it along with a couple of currently unused columns that
we can use for interim processing. To get the code into your workbook: Open
it, press [Alt]+[F11] to get to the VB Editor, use Insert -- Module to
create an empty code module. Copy and paste the code below into that module
and edit it as needed. Close the VB editor. Choose the sheet with the 3000
numbers on it and use Tools -- Macro -- Macros to select and [Run] the code.

Sub CullTheList()
'be sure the sheet with the list
'to work with is selected when
'you use:
' Tools -- Macro -- Macros
'to run this process.
'
'ALSO -- CLEAR OUT any existing
'entries in columns B and C (or as
'changed in code below) to remove
'any previous results of this
'process

'change these constant values as required
'This one points to your original List column
Const originalColumn = "A"
'a column available for use
Const firstCutColumn = "B"
'a second column for final results
Const secondCutColumn = "C"
'this is the first row with a
'number to examine in it
Const firstEntryRow = 2

Dim originalList As Range
Dim anyEntry As Range

Set originalList = ActiveSheet _
.Range(originalColumn & firstEntryRow & ":" _
& ActiveSheet.Range(originalColumn & _
Rows.Count).End(xlUp).Address)
'take the first cut, move entries whose
'2nd/3rd characters have value .gt. 12
For Each anyEntry In originalList
If Val(Mid(anyEntry, 2, 2)) 12 Then
ActiveSheet.Range(firstCutColumn & _
Rows.Count).End(xlUp).Offset(1, 0) = _
anyEntry
End If
Next
'change originalList to pick up on
'entries now in column B
Set originalList = ActiveSheet _
.Range(firstCutColumn & firstEntryRow & ":" _
& ActiveSheet.Range(firstCutColumn & _
Rows.Count).End(xlUp).Address)
'move any entries with 4th character
'NOT an 8 or 9 to column C
For Each anyEntry In originalList
If Val(Mid(anyEntry, 4, 1)) < 8 And _
Val(Mid(anyEntry, 4, 1)) < 9 Then
ActiveSheet.Range(secondCutColumn & _
Rows.Count).End(xlUp).Offset(1, 0) = _
anyEntry
End If
Next
Set originalList = Nothing ' housekeeping
MsgBox "Culled List is now in column C", vbOKOnly, _
"Job Done"

End Sub


"Dave" wrote:

Hi Gang:

Need some help on a query I have a long list of 3000 numbers. like
this

91290007957
91290007977
91290008257
91400000000
92400000000
01500000000
91401039801
90550000000
00160000000
91401050593

I want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000

then of those that are left I would sort off any that the 4th number
was not an 8 or 9

90550000000
00160000000

Sincerely,


thanks in advance


Dave





.

Help on a task
 

On Jan 25, 4:41*pm, JLatham wrote:
I have assumed that the entries are text for this solution. *And either I've
got it right, or I have it 100% bass-akwards in the results. *But if that's
the case, then some simple logic changes to the code will get it right.

MAKE A COPY of your workbook. *We don't want to mess up your list of numbers
just yet. *In theory, this code should leave the original list untouched, but
you know the old adage about best laid plans and all, so work from a copy
initially.

Here's code. *You'll need to edit some values in it to identify the column
with the numbers in it along with a couple of currently unused columns that
we can use for interim processing. *To get the code into your workbook: *Open
it, press [Alt]+[F11] to get to the VB Editor, use Insert -- Module to
create an empty code module. *Copy and paste the code below into that module
and edit it as needed. *Close the VB editor. *Choose the sheet with the 3000
numbers on it and use Tools -- Macro -- Macros to select and [Run] the code.

Sub CullTheList()
'be sure the sheet with the list
'to work with is selected when
'you use:
' Tools -- Macro -- Macros
'to run this process.
'
'ALSO -- CLEAR OUT any existing
'entries in columns B and C (or as
'changed in code below) to remove
'any previous results of this
'process

* 'change these constant values as required
* 'This one points to your original List column
* Const originalColumn = "A"
* 'a column available for use
* Const firstCutColumn = "B"
* 'a second column for final results
* Const secondCutColumn = "C"
* 'this is the first row with a
* 'number to examine in it
* Const firstEntryRow = 2

* Dim originalList As Range
* Dim anyEntry As Range

* Set originalList = ActiveSheet _
* *.Range(originalColumn & firstEntryRow & ":" _
* * & ActiveSheet.Range(originalColumn & _
* * Rows.Count).End(xlUp).Address)
* 'take the first cut, move entries whose
* '2nd/3rd characters have value .gt. 12
* For Each anyEntry In originalList
* * If Val(Mid(anyEntry, 2, 2)) 12 Then
* * * ActiveSheet.Range(firstCutColumn & _
* * * *Rows.Count).End(xlUp).Offset(1, 0) = _
* * * * anyEntry
* * End If
* Next
* 'change originalList to pick up on
* 'entries now in column B
* Set originalList = ActiveSheet _
* *.Range(firstCutColumn & firstEntryRow & ":" _
* * & ActiveSheet.Range(firstCutColumn & _
* * Rows.Count).End(xlUp).Address)
* 'move any entries with 4th character
* 'NOT an 8 or 9 to column C
* For Each anyEntry In originalList
* * If Val(Mid(anyEntry, 4, 1)) < 8 And _
* * *Val(Mid(anyEntry, 4, 1)) < 9 Then
* * * ActiveSheet.Range(secondCutColumn & _
* * * *Rows.Count).End(xlUp).Offset(1, 0) = _
* * * * anyEntry
* * End If
* Next
* Set originalList = Nothing ' housekeeping
* MsgBox "Culled List is now in column C", vbOKOnly, _
* *"Job Done"

End Sub



"Dave" wrote:
Hi Gang:

Need some help on a query I have a long list of *3000 numbers. like
this

91290007957
91290007977
91290008257
91400000000
92400000000
01500000000
91401039801
90550000000
00160000000
91401050593

I *want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000

then of those that are left I *would sort off any that the 4th number
was not an 8 or 9

90550000000
00160000000

Sincerely,

thanks *in advance

Dave

.- Hide quoted text -

- Show quoted text -

Here are the answers:

1 Cells are formatted as TEXT with leading zeros, which are part of
the number.

2 Yes, remove them from the list please,


thanks so much for your help.

Help on a task
 

Then this code should work, again, change the two Const values that are now
="A" and =2 to what ever they should be to work in your workbook. Because
this is now definitely a 'destructive' process, you really should work from a
copy of the workbook.

From your sample list of 10 numbers, I was left with 3:
91290007957
91290007977
91290008257

Sub CullTheList()
'be sure the sheet with the list
'to work with is selected when
'you use:
' Tools -- Macro -- Macros
'to run this process.
'
'change these constant values as required
'This one points to your original List column
Const originalColumn = "A"
'this is the first row with a
'number to examine in it
Const firstEntryRow = 2

Dim lastRow As Long
Dim RPointer As Long

lastRow = ActiveSheet.Range(originalColumn & _
Rows.Count).End(xlUp).Row
'take the first cut, Remove entries whose
'2nd/3rd characters have value .gt. 12
For RPointer = lastRow To firstEntryRow Step -1
If Val(Mid(Range(originalColumn & _
RPointer), 2, 2)) 12 Then
Range(originalColumn & RPointer) _
.EntireRow.Delete
End If
Next
lastRow = ActiveSheet.Range(originalColumn & _
Rows.Count).End(xlUp).Row

'change originalList to pick up on
'entries now in column B
'remove any entries with 4th character
'NOT an 8 or 9
For RPointer = lastRow To firstEntryRow Step -1
If Val(Mid(Range(originalColumn & _
RPointer), 4, 1)) < 8 And _
Val(Mid(Range(originalColumn & _
RPointer), 4, 1)) < 9 Then
Range(originalColumn & RPointer) _
.EntireRow.Delete
End If
Next

MsgBox "List has been reduced.", vbOKOnly, _
"Job Done"

End Sub


"Dave" wrote:

On Jan 25, 4:41 pm, JLatham wrote:
I have assumed that the entries are text for this solution. And either I've
got it right, or I have it 100% bass-akwards in the results. But if that's
the case, then some simple logic changes to the code will get it right.

MAKE A COPY of your workbook. We don't want to mess up your list of numbers
just yet. In theory, this code should leave the original list untouched, but
you know the old adage about best laid plans and all, so work from a copy
initially.

Here's code. You'll need to edit some values in it to identify the column
with the numbers in it along with a couple of currently unused columns that
we can use for interim processing. To get the code into your workbook: Open
it, press [Alt]+[F11] to get to the VB Editor, use Insert -- Module to
create an empty code module. Copy and paste the code below into that module
and edit it as needed. Close the VB editor. Choose the sheet with the 3000
numbers on it and use Tools -- Macro -- Macros to select and [Run] the code.

Sub CullTheList()
'be sure the sheet with the list
'to work with is selected when
'you use:
' Tools -- Macro -- Macros
'to run this process.
'
'ALSO -- CLEAR OUT any existing
'entries in columns B and C (or as
'changed in code below) to remove
'any previous results of this
'process

'change these constant values as required
'This one points to your original List column
Const originalColumn = "A"
'a column available for use
Const firstCutColumn = "B"
'a second column for final results
Const secondCutColumn = "C"
'this is the first row with a
'number to examine in it
Const firstEntryRow = 2

Dim originalList As Range
Dim anyEntry As Range

Set originalList = ActiveSheet _
.Range(originalColumn & firstEntryRow & ":" _
& ActiveSheet.Range(originalColumn & _
Rows.Count).End(xlUp).Address)
'take the first cut, move entries whose
'2nd/3rd characters have value .gt. 12
For Each anyEntry In originalList
If Val(Mid(anyEntry, 2, 2)) 12 Then
ActiveSheet.Range(firstCutColumn & _
Rows.Count).End(xlUp).Offset(1, 0) = _
anyEntry
End If
Next
'change originalList to pick up on
'entries now in column B
Set originalList = ActiveSheet _
.Range(firstCutColumn & firstEntryRow & ":" _
& ActiveSheet.Range(firstCutColumn & _
Rows.Count).End(xlUp).Address)
'move any entries with 4th character
'NOT an 8 or 9 to column C
For Each anyEntry In originalList
If Val(Mid(anyEntry, 4, 1)) < 8 And _
Val(Mid(anyEntry, 4, 1)) < 9 Then
ActiveSheet.Range(secondCutColumn & _
Rows.Count).End(xlUp).Offset(1, 0) = _
anyEntry
End If
Next
Set originalList = Nothing ' housekeeping
MsgBox "Culled List is now in column C", vbOKOnly, _
"Job Done"

End Sub



"Dave" wrote:
Hi Gang:

Need some help on a query I have a long list of 3000 numbers. like
this

91290007957
91290007977
91290008257
91400000000
92400000000
01500000000
91401039801
90550000000
00160000000
91401050593

I want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000

then of those that are left I would sort off any that the 4th number
was not an 8 or 9

90550000000
00160000000

Sincerely,

thanks in advance

Dave

.- Hide quoted text -

- Show quoted text -

Here are the answers:

1 Cells are formatted as TEXT with leading zeros, which are part of
the number.

2 Yes, remove them from the list please,


thanks so much for your help.
.

Help on a task
 

On Jan 25, 8:49*pm, JLatham wrote:
Then this code should work, again, change the two Const values that are now
="A" and =2 to what ever they should be to work in your workbook. *Because
this is now definitely a 'destructive' process, you really should work from a
copy of the workbook.

From your sample list of 10 numbers, I was left with 3:
91290007957
91290007977
91290008257

Sub CullTheList()
'be sure the sheet with the list
'to work with is selected when
'you use:
' Tools -- Macro -- Macros
'to run this process.
'
* 'change these constant values as required
* 'This one points to your original List column
* Const originalColumn = "A"
* 'this is the first row with a
* 'number to examine in it
* Const firstEntryRow = 2

* Dim lastRow As Long
* Dim RPointer As Long

* lastRow = ActiveSheet.Range(originalColumn & _
* * Rows.Count).End(xlUp).Row
* 'take the first cut, Remove entries whose
* '2nd/3rd characters have value .gt. 12
* For RPointer = lastRow To firstEntryRow Step -1
* * If Val(Mid(Range(originalColumn & _
* * *RPointer), 2, 2)) 12 Then
* * * Range(originalColumn & RPointer) _
* * * *.EntireRow.Delete
* * End If
* Next
* lastRow = ActiveSheet.Range(originalColumn & _
* * Rows.Count).End(xlUp).Row

* 'change originalList to pick up on
* 'entries now in column B
* 'remove any entries with 4th character
* 'NOT an 8 or 9
* For RPointer = lastRow To firstEntryRow Step -1
* * If Val(Mid(Range(originalColumn & _
* * *RPointer), 4, 1)) < 8 And _
* * *Val(Mid(Range(originalColumn & _
* * *RPointer), 4, 1)) < 9 Then
* * * Range(originalColumn & RPointer) _
* * * *.EntireRow.Delete
* * End If
* Next

* MsgBox "List has been reduced.", vbOKOnly, _
* *"Job Done"

End Sub



"Dave" wrote:
On Jan 25, 4:41 pm, JLatham wrote:
I have assumed that the entries are text for this solution. *And either I've
got it right, or I have it 100% bass-akwards in the results. *But if that's
the case, then some simple logic changes to the code will get it right.