Knowledgebase
Statement Converter > Statement Converter Support > Knowledgebase

Search help:



Two-part Lookup feature ($)

Solution

The standard Lookup feature makes one comparison in the lookup table.  An optional feature is available to make two comparisons.  (This feature is available in version 3.2.18 and later).

1.  Purchase the option

From the Statement Converter order menu, order the "Two-part Lookup" option.  Upon receipt of your order, you will be provided a code to enable the feature.

2.  Set the comparison column

a.  Determine which column in your statement (transactions) file has the text for the second comparison.  This would typically be the Payee, Description or Memo column.

b.  Using a text editor (such as NotePad), open Statement Converter's "system" config file, SC_config_System.txt

c.  Enter this setting with the column you selected in step 2a:  

# set the two-part lookup option
Lookup_Comparison_Column_2 = 

(Tip: copy and paste this text into your config file. The first line beginning with the hash mark is only a comment and you may change it.)

Examples:

1.  if you want to search the Payee text in your transactions and the payees are in column B of your statement, enter this:

Lookup_Comparison_Column_2 = B

2.  if you want to search the Memo text of your transcactions and the memos are in column F of your statement file, enter this:

Lookup_Comparison_Column_2 = F

d.  Save the changes and close the file

e.  Re-start Excel (when changes are made to the system config file, Excel must be restarted)

3.  Add a column to the Lookup table

a.  Using Excel, open the Lookup table (e.g. Vendor_lookup.xls)

b.  Insert a new column B by selecting column B and clicking Insert > Column.  (In other words, shift the existing columns B, C, D, etc. to the right so that there is new, empty column B.)  Note: this column "B" is completely separate from whatever column you specified in step #2, above.

c.  You may enter any label you wish in header cell B1.

d.  Save the changes.  You may leave the file open for further editing or close it.

4.  Enter your matching rules

Let's see how to create the matching rules for two similar transactions on your statement.  You will enter your matching rules using columns A and B of the Lookup table, like this:

Example:

Say you have two deposit transactions, each to a different account. One transaction says "Deposit to Checking, Account #1234" and the second transaction says "Deposit to Checking, Account #5678".  Using the sample lookup rules shown above, you could enter the matching rules like this:

"If the payee text contains 'Deposit to Checking' AND the payee text contains '#1234', then assign the Vendor name 'Cash Sale' and post it to the 'Sales' account."

"If the payee text contains 'Deposit to Checking' AND the payee text contains '#5678', then assign the Vendor name 'Credit card payment' and post it to the 'Sales' account."

Your matching rules can be one- or two-part rules, using one column or both columns.  If you enter text in both columns A and B, both items have to match in order for the rule to be used.  If you enter text in only one column, A or B, only that one item has to match for the rule to be used. In other words, the program performs an "AND" comparison.

 

5.  Enable the 2-column lookup feature

a.  From the Excel menu, select Convert to QB > View config...

b.  In the "Overrides" section at the bottom right, locate the field "Option" and enter the option code provided by Statement Converter support:

c.  enable the Lookup feature as needed for your situation:  

d.  Set the Lookup table:  As shown above, in the section "Lookup Feature", checkmark "Use Lookup" and make sure the filename and extension are correct (e.g.  Vendor_lookup.xls).

e.  Click "Save changes" and "Close"  (Excel does NOT need to be restarted.)

6.  Convert your statement
 
Select "Convert to QB" from the Excel menu.
 
 
If you have any questions, please submit a support ticket.
 
Technical Note: the 2-part lookup compares lookup-table column A to the transactions Payee/description and lookup-table column B to the Memo (whatever the designated, second field is).  Next, it will attempt to match only A and then only B.
Note:  General instructions on using the standard Lookup feature are HERE.
 
 
Was this article helpful? yes / no
Article details
Article ID: 52
Category: Configuring / Customizing the Converter
Rating (Votes): Article rated 3.1/5.0 (30)

 
« Go back

 
Powered by Help Desk Software HESK - brought to you by Help Desk Software SysAid