Help on a task

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

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
 
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.
 
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
 
I have assumed that the entries are text for this solution.  And eitherI'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 said:
Hi Gang:
Click to expand...
Need some help on a query I have a long list of  3000 numbers. like
this

I  want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000
Click to expand...
then of those that are left I  would sort off any that the 4th number
was not an 8 or 9


thanks  in advance

.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
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 said:
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 said:
Hi Gang:
Click to expand...
Need some help on a query I have a long list of 3000 numbers. like
this

I want to sort off any numbers that the 2nd and 3rd numbers are
greater then 12.
Example would be:
91400000000
92400000000
01500000000
Click to expand...
then of those that are left I would sort off any that the 4th number
was not an 8 or 9


thanks in advance

.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
.
Click to expand...
 
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 said:
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 thecolumn
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
:
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 -
Click to expand...
Click to expand...
Here are the answers:
Click to expand...
1 Cells are formatted as TEXT with leading zeros, which are part of
the number.
Click to expand...
2  Yes, remove them from the list please,
Click to expand...
thanks so much for your help.
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi thanks, works like a charm, I would not have ever figured it out. I
was trying to use a function or something. Thanks again, you are a
lifesaver!!!
 
Back
Top