How to Repeat Until %T1% = %T2%

[gorilla]

Hello, 

I'm here to ask help to make a Macro script what I'm struggling.
Here's my situation.

I have a Excel sheet like this and "A column" is important. Each row of "A column" contains a text string(XXXX-XXXX) and the total number of rows is 600. 

A column        B col    C col    D col    E col

1000-1500    text1    text2    number1    number2
1000-1501    text1    text2    number1    number2
~
1000-1599    text1    text2    number1    number2
1000-1800    text1    text2    number1    number2
1000-1801    text1    text2    number1    number2
~
1000-1899    text1    text2    number1    number2
1000-3500    text1    text2    number1    number2
~
1000-3699    text1    text2    number1    number2
2000-5400    text1    text2    number1    number2
~
2000-5599    text1    text2    number1    number2

And I have a text file "list-a.txt" which consists of roughly 300 lines and each line has a text string(XXXX-XXXX). 
1000-1504
1000-1507
1000-1810
2000-5411
2000-5599

What I need is to delete data in column (B:E) of a row in Excel if the text sting XXXX-XXXX is NOT in "list-a.txt".
And I need to type/input data in column (B:E) if the text string XXXX-XXXX is IN "list-a.txt".

For example,
if "list-a.txt" file has only 5 lines as above.
The Excel sheet has to be the same as the following.

A column        B col    C col    D col    E col

1000-1500
1000-1501
1000-1502
1000-1503
1000-1504    newT1    newT2    newN1    newN2
1000-1505
1000-1507    newT1    newT2    newN1    newN2
1000-1508
~
2000-5410
2000-5411    newT1    newT2    newN1    newN2
~
2000-5598
2000-5599    newT1    newT2    newN1    newN2

I used "Text File Begin Process" command from file "list-a.txt" and Set Variable to %T1%. And tried with many more combinations like "Repeat Until" command but all failed.
Please help me out. 

* Windows 7 and Macro Express v3.10.2.1

[rberq]

I think this approach will work:  Use Text File Begin Process, once only, to load all entries from the file into a single text variable.  That is, read the file one line at a time into T1, trim, and append its 9 characters to T2 (variable modify string/append), continue until the whole file has been loaded.  When done, T2 will be an array of 9-character strings.  (But you won’t need to process it as an array – see below.)  

Next, copy the first spreadsheet cell into the clipboard, store in text variable T3.  Command
If Variable %T2% contains "%T3%" 
tells you whether the array (file) contains that cell’s data, and you can type or delete into other cells in the row as desired.  Text Type of arrow keys is good for moving from one spreadsheet cell to another.  Get this working for a single row of the spreadsheet.  Once you have it working for one row, add Repeat Start and Repeat End to handle all the rows one by one.  Use Repeat Exit when you reach the end of the spreadsheet (that is, when copy to clipboard finds nothing).  

 

[gorilla]

14 hours ago, rberq said:

I think this approach will work:  Use Text File Begin Process, once only, to load all entries from the file into a single text variable.  That is, read the file one line at a time into T1, trim, and append its 9 characters to T2 (variable modify string/append), continue until the whole file has been loaded.  When done, T2 will be an array of 9-character strings.  (But you won’t need to process it as an array – see below.)  

Next, copy the first spreadsheet cell into the clipboard, store in text variable T3.  Command
If Variable %T2% contains "%T3%" 
tells you whether the array (file) contains that cell’s data, and you can type or delete into other cells in the row as desired.  Text Type of arrow keys is good for moving from one spreadsheet cell to another.  Get this working for a single row of the spreadsheet.  Once you have it working for one row, add Repeat Start and Repeat End to handle all the rows one by one.  Use Repeat Exit when you reach the end of the spreadsheet (that is, when copy to clipboard finds nothing).  

 

Here's my script which is inspired by your kind comment. Thanks a lot.

Text File Begin Process: "list-a.txt"
 Repeat Until %T2% = %T1%
  Clipboard Copy
  Variable Set String %T2% from Clipboard
  Variable Modify String: Trim %T2%
   If Variable %T2% <> variable %T1%
    Text Type: <ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><HOME><ARROW DOWN>
   Else
    Text Type: <ARROW DOWN>
   End If
 Repeat End
Text File End Process

But There's 1 problem here.
The last line of the "list-a.txt" is 2000-5592,
and the last row of Excel is 2000-5599.

This script ends at 2000-5592 and can't delete rows from 2000-5593 to 2000-5599.
I need to delete data if the rows doens't match with the data in the list.

I would appreciate it if you give me more help.

 

[rberq]

I think you are almost there, but you missed the whole point of FIRST loading the entire text file into a single variable, then using a Repeat loop to process the spreadsheet rows one at a time.  Here's your script modified a little:

Text File Begin Process: "list-a.txt"
  Variable Modify String: Trim %T1%
  Variable Modify String: Append %T1% to %T2%
Text File End Process
* at this point the whole file is stacked in variable T2

Repeat Until 1=2 (in other words, repeat forever until the IF statement results in Repeat Exit)
 Clipboard Copy
  If clipboard = “” (nulls – all spreadsheet have been processed)
   Repeat Exit
  End If
 Variable Set String %T3% from Clipboard
 Variable Modify String: Trim %T3%
  If Variable %T2% contains variable %T3%
   Text Type: <ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><ARROW RIGHT><DELETE><HOME><ARROW DOWN>
  Else
   Text Type: <ARROW DOWN>
  End If
Repeat End

 

[gorilla]

Wow, It works !!!

And It works in other possible case as well. Excellent. 

THANKS for your great help.