Better Way To Do This SLOW code

[rshow1971]

Please help me.

I am importing two .csv files from an Oracle table into a workbook:
SalesRep, MainRep

Column A: is Bank
Column B: is Rep Name
Column C: is Branch #
Columns N:R contain data.

The process is to compare the MainRep page to the SalesRep page.
So for each row on the MainRep page, If the Bank and Rep Name and
Branch #, are
found on the SalesRep page, do nothing, else, add the entire row to the
SalesRep page.

Currently I have declared the 3 ranges on the SalesRep page and
inserted an Array formula.
in the next empty column of row 1.
Sheets("SalesRep").Cells(1, NxtCol).FormulaArray = "=MATCH(R[0]C[1]&
R[0]C[2]&R[0]C[3],Bank & SalesAENames & SalesBranchNum,0)"

and for each cell in the MainRep page I add the cell values so the
formula will compute
Sheets("SalesRep").Cells(1, NxtCol + 1).Value = Bank
Sheets("SalesRep").Cells(1, NxtCol + 2).Value = AeName
Sheets("SalesRep").Cells(1, NxtCol + 3).Value = AeBranch

next piece is to check if formula gives error(means I have to add the
rep and bank and branch, etc.)
If IsError(Sheets("SalesRep").Cells(1, NxtCol).Value) Then
add the current row from the mainrep page.
else
do nothing.

this was ok when i tested with my data, however, the oracle table
results are 17,000
records, and it takes a long time to process. Any idea on a better way
to compare these values? any help would be greatly appreciated.

Thank you.
Ron.

 

[Randy Hudson]

I am importing two .csv files from an Oracle table into a workbook:
SalesRep, MainRep

Column A: is Bank
Column B: is Rep Name
Column C: is Branch #
Columns N:R contain data.

The process is to compare the MainRep page to the SalesRep page.
So for each row on the MainRep page, If the Bank and Rep Name and
Branch #, are
found on the SalesRep page, do nothing, else, add the entire row to the
SalesRep page.

this was ok when i tested with my data, however, the oracle table results
are 17,000 records, and it takes a long time to process. Any idea on a
better way to compare these values? any help would be greatly
appreciated.

Can you do the Join in a temporary table in Oracle, then read that table
into your spreadsheet? In general, a database will be faster at doing a
join than a spreadsheet will.