How to Search and Replace

Geenie

Member
This may be a simple question to some of
you but I need some help.

I have a text file (A) with all my lottery lines of numbers.

I also have my elimination text file with lottery numbers (B) I want to delete from (A)

How do I do this easily?

:rolleyes:
 

thornc

Member
Well depending on what your elimination file contains you could code a batch file to do the job....

Better yet if by any change you know Linux you could use something like awk or sed to it even simpler...

Of course Perl could be an option!!

It all depends on your input and on your knowledge!
 

thornc

Member
simple things first!

Ok, starting with the simple things!

Can you tell me exactly what's the contents of these files??

A batch file is just a series of dos commands that are executed on the file is called.

Linux is an operating system with very good progrsmming capabilities when is comes to batch files!

Perl = Pratical Extraction and Report Language, is a script based language developed to ease taks such as this...

Hence the questions...
Do you know enough about computers, programming to look at the more advanced stuff... if not your best bet would be a simple aproach like those batch files and perhaps excell!?
 

Geenie

Member
thornc

You may be correct in stating I don't "know the advanced stuff" I'm a graphic designer/illustrator for over 10 years and programming is not necessary fortunately.

I would like the KISS method please!!!
Give me the simple approach like those batch files
or better yet Excel. Pretend you are relaying instructions to a 12 year old and I think I will
understand. I appreciate you time thornc.

I have a text file eg. say I named the file original.txt
3,24,27,29,32,45
3,11,12,25,32,38
(or with spaces)

Then I have another text file (same format) with lines of lottery numbers I want to eliminate from my first file. eg. elimination.txt

original.txt minus elimination.txt = numbers I want to play.

Somtimes I will have hundreds I want to eliminate and it would take me to long one by one.
I hope I made myself clearer.






:)
 

GillesD

Member
Eliminating some lines

If your two files are exactly identical in format and content, maybe Excel can help you.

Lets say you have 5 lines you want eliminated from 100 lines in a text file.

Import your elimination.txt into Excel at cell A1 so you have 5 lines (A1 to A5) each with one line you want eliminated.

Import your original.txt into Excel at cell A10 so you have 100 lines (A10 to A109) each with one line from which you want to remove some.

In cell B10, enter the formula =IF(OR(A10=$A$1,A10=$A$2,A10=$A$3,A10=$A$4,A10=$A$5),1,0)

Please note that a correction was made in the formula (one $A10 changed to A10) but the first formula should work as well.

Copy this formula down column B to B109.

If any line in A10 to A109 is exactly the same as those in A1 to A5, a 1 will appear in column B, otherwise you will have a 0. Using the automatic filtering, select to show only those lines with a 1. When you have this, remove those lines.

I agree this might be a little long if you have 100s of lines to eliminate. If you want, you may put up to 30 parameters in the OR function (each separated by a ,). In the example given, there are 5 parameters (A10=$A$1 being the first one and the last one is A14=$A$5).

I hope this may fit youe needs.:agree2:
 
Last edited:

Geenie

Member
Gilles

Thanks Gilles. I will try this. I have to add that I was practising mind thoughts that you will
post a response after thornc and it worked. I know it sounds weird and "out there" but some members of my family do this quite often and this was my first time. Maybe I'll try it for the lottery. I'm going to win this Wed. I'm going to win this Wed.........



:agree: :agree: :agree: :agree: :agree:
 

GillesD

Member
NmbrsDude

Yes you are right NmbrsDude and my post will be corrected.

Although it will not matter really since when you copy it to B11, B12, etc., it will still refer to the proper cell in column A. But it should not be there. Thanks
 

Geenie

Member
Gilles or NumbrsDude:

Can you tell me if this is what I want and how to I insert this code.


I have the goods you want in a file, I do believe.

This code will go through all of the values in ONE column, and delete ALL
of the duplicates. In other words, if 6 records start with 12345, then all 6
records are deleted, NOT just the 5 duplicates.


Here's the code:
Sub DeleteDuplicateRows()

Dim rng As Range
Dim cl As Range
Dim rngOriginal As Range
Dim rngDups As Range
Dim strCol As String
Dim strRangeErr As String

strRangeErr = "Error with your range, please try again"

On Error Resume Next
Set rngOriginal = Selection
Set rng = Application.InputBox("Please select the range that you would
like to " & _
"delete rows from - please make sure that you only select ONE
column " & _
"in your range.", "Select Range", , , , , , 8)
If Err <> 0 Then
MsgBox strRangeErr, vbCritical, "Exiting..."
GoTo ExitHere
ElseIf rng Is Nothing Then
MsgBox strRangeErr, vbCritical, "Exiting..."
GoTo ExitHere
ElseIf rng.Columns.Count > 1 Then
MsgBox "You selected a range that has more than one column -
please " & _
"re-run this program and select only one column.", vbCritical,
"Exiting..."
GoTo ExitHere
ElseIf rng.Rows.Count <= 1 Then
MsgBox "There are no duplicates in one cell! Please try again and
select more " & _
"than one cell.", vbCritical, "Exiting..."
GoTo ExitHere
End If
On Error GoTo HandleErr

Application.ScreenUpdating = False

rng.Range("A1").Offset(0, 1).Select
Selection.EntireColumn.Insert
ActiveCell.Formula = "=COUNTIF(" & rng.Address & "," & _
Application.ConvertFormula(rng.Range("A1").Address, xlA1, xlA1,
xlRelative) & _
")"
Selection.AutoFill _
Destination:=Range(rng.Range("A1").Offset(0, 1), _
rng(rng.Rows.Count, rng.Columns.Count).Offset(0, 1)), _
Type:=xlFillDefault

For Each cl In _
Range(rng.Range("A1").Offset(0, 1), rng(rng.Rows.Count,
rng.Columns.Count).Offset(0, 1))
If cl.Value > 1 Then
If rngDups Is Nothing Then
Set rngDups = Range(cl.Address)
Else
Set rngDups = Application.Union(rngDups, Range(cl.Address))
End If
End If
Next cl

rngDups.EntireRow.Delete
rng.Offset(0, 1).EntireColumn.Delete
If Not (rngOriginal Is Nothing) Then
rngOriginal.Select
Else
Range("A1").Select
End If

Application.ScreenUpdating = True

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox Err.Description, vbCritical, "Error in DeleteDuplicateRows"
Resume ExitHere
End Select

End Sub
 

Geenie

Member
I finally figured it out if anyone wants to use
the above code and is not too knowledgeable with Excel like me!


1- Start Excel
2- Open your file
3- Press Alt+F11 (VBE is opened)
4- Click Insert_Module (You have a new module)
5- Put this code in that module
6- Press F5 or Goto Excel Tools_Macro_Macros, select this macro name in
macro list and Run. Or put a command button on sheet and assign this
macro (make your button Don't move or size with cells)

:)
 

Sidebar

Top