Comparing data on two tabs

[Dan McCollick]

Hi all,
I have two spreadsheets that I import into two seperate tabs in my
workbook. The goal is to list projects that match from one sheet to
the other, as well as a seperate section for projects that do not
match(reconciliation). I currently have a macro that pulls the data
into two seperate arrays. My question is, is there a function in excel
that would do this for me(dsum??)? The macro takes almost 5 mins to
run(~400 items in each spreadsheet). My sort algorithm maybe what is
slowing down the process.

Thanks
Dan

Ps. I wanted to do this project in access, but was denied. Has to be
in excel.

 

[Guest]

Dan,
Can you post a sample of your code? 5 minutes seems very excessive
to process such a small number of items.

 

[Tom Ogilvy]

If you want to do joins on your data, you should be able to do it with
Data=>Get External Data and then query your workbook.

I believe you can do this even internal to the source workbook, but
certainly can do it from another workbook.

Also, you can look at Chip Pearson's site for working with this/similar
situations

http://www.cpearson.com/excel/duplicat.htm

or for Code, look at this Post by KeepItCool

http://groups.google.com/group/micr...mming/msg/7e348d43cf5073a2?dmode=source&hl=en

 

[Dan McCollick]

Thanks for the replies so far. Tom, I saw your post linking to ADO.
And this seems much more familiar to me(as i could use sql). One
problem I am encountering so far. I can not return a recordset
containing mulitple records?? here is my code so far

Private Sub getData(sourceFile As String, SourceRange As String,
TargetRange As Range, IncludeFieldNames As Boolean, TypeofClass As
String)




Dim TargetCell As Range
Dim i As Integer

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" &
"ReadOnly=1;DBQ=" & sourceFile
Set dbConnection = New ADODB.Connection

On Error GoTo InvalidConnection
dbConnection.Open dbConnectionString 'open the database
'Set rs = dbConnection.Execute("[" & SourceRange & "]")
Dim rstTables As ADODB.Recordset
Set rstTables = dbConnection.OpenSchema(adSchemaTables)
Set rs = New ADODB.Recordset
SQL = "Select * FROM " & SourceRange

rs.Open SQL, dbConnection

Set TargetCell = TargetRange.Cells(1, 1)

If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
If TypeofClass = "Prosight" Then
While Not rs.EOF
TargetCell.Offset(0, 0).Formula = rs.Fields(1).Value
TargetCell.Offset(0, 1).Formula = rs.Fields(10).Value
TargetCell.Offset(0, 2).Formula = rs.Fields(0).Value
TargetCell.Offset(0, 3).Formula = rs.Fields(31).Value

rs.NextRecordset
Wend
End If

'TargetCell.CopyFromRecordset rs

Exit Sub

InvalidConnection:
MsgBox Err.Description, vbExclamation, "Incorrect Data"
End Sub

The Err.Description = "Current Provider does not support returning
mulitple recordsets from a single execution"

 

[Dan McCollick]

Update: My error was in using rs.nextrecordset. I should have been
using rs.movenext(to cylce to next record within the recordset).