Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data on two tabs
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data on two tabs
Dan,
Can you post a sample of your code? 5 minutes seems very excessive to process such a small number of items. "Dan McCollick" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data on two tabs
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/micro...e=source&hl=en -- Regards, Tom Ogilvy "Dan McCollick" wrote in message oups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data on two tabs
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing data on two tabs
Update: My error was in using rs.nextrecordset. I should have been
using rs.movenext(to cylce to next record within the recordset). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Tabs | Excel Worksheet Functions | |||
comparing two columns of data and return unique data in another co | Excel Discussion (Misc queries) | |||
Comparing similar data on different sheet tabs | Excel Discussion (Misc queries) | |||
comparing lists of data to remove duplicate data | Excel Discussion (Misc queries) | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions |