Comparing 2 sets of data

S

smoothlarryhughes

New Member
Joined
Feb 12, 2009
Messages
39
Hello,

I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do:

File 1:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Identifier</TD><TD>Shares 1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">100000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">222</TD><TD style="TEXT-ALIGN: right">200000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">333</TD><TD style="TEXT-ALIGN: right">300000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">444</TD><TD style="TEXT-ALIGN: right">400000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">555</TD><TD style="TEXT-ALIGN: right">500000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">666</TD><TD style="TEXT-ALIGN: right">600000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">777</TD><TD style="TEXT-ALIGN: right">700000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">888</TD><TD style="TEXT-ALIGN: right">800000</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

File 2:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Identifier</TD><TD>Shares 2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">333</TD><TD style="TEXT-ALIGN: right">300000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">444</TD><TD style="TEXT-ALIGN: right">400000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">555</TD><TD style="TEXT-ALIGN: right">800000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">666</TD><TD style="TEXT-ALIGN: right">600000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">777</TD><TD style="TEXT-ALIGN: right">700000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">888</TD><TD style="TEXT-ALIGN: right">800000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">999</TD><TD style="TEXT-ALIGN: right">50000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">75000</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Want to create this without losing any data from either file:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Identifier</TD><TD>Shares 1</TD><TD>Shares 2</TD><TD>Difference</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">111</TD><TD style="TEXT-ALIGN: right">100000</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">100000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">222</TD><TD style="TEXT-ALIGN: right">200000</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">200000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">333</TD><TD style="TEXT-ALIGN: right">300000</TD><TD style="TEXT-ALIGN: right">300000</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">444</TD><TD style="TEXT-ALIGN: right">400000</TD><TD style="TEXT-ALIGN: right">400000</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">555</TD><TD style="TEXT-ALIGN: right">500000</TD><TD style="TEXT-ALIGN: right">800000</TD><TD style="TEXT-ALIGN: right">-300000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">666</TD><TD style="TEXT-ALIGN: right">600000</TD><TD style="TEXT-ALIGN: right">600000</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">777</TD><TD style="TEXT-ALIGN: right">700000</TD><TD style="TEXT-ALIGN: right">700000</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">888</TD><TD style="TEXT-ALIGN: right">800000</TD><TD style="TEXT-ALIGN: right">800000</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">999</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">50000</TD><TD style="TEXT-ALIGN: right">-50000</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">75000</TD><TD style="TEXT-ALIGN: right">-75000</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi,

Excel can do the same queries as Access; so it is just a matter of implementation. Such as whether to use query tables, or ADO recordsets or whatever.

As you're not familiar with doing this in Excel and are familiar with Access, I suggest you use Access.

HTH, Fazza
 
Upvote 0
I am still confused how to capture all the data from both files into one file. Any help on how I can do this? How can I query two files using excel?
 
Upvote 0
Include the full path in the reference. Such as
Code: 
SELECT tbl1.*, tbl2.*
FROM `C:\path\excel1.xls`.table1 tbl1, `D:\otherpath\excel2.xls`.table2 tbl2
WHERE tbl1.code = tbl2.code
That is just generic & untested. Per your original question, though, if this isn't familiar it would seem better to use Access. Of if using ADO & recordsets it will be virtually identical VBA in Excel and Access.
 
Upvote 0
I tried this and it will only pull the like identifiers from both tables. I need it to show even the identifiers that are not alike. Thoughts?

SELECT Custodian.CUSIP, Custodian.[Custodian Holding], Hiport.CUSIP, Hiport.[Fund Holding]
FROM Hiport INNER JOIN Custodian ON Hiport.CUSIP = Custodian.CUSIP;
 
Upvote 0
Use UNION ALL to combine the three possibilities. Such as,

SELECT records common to both tables {your inner join as above}
UNION ALL records in table 1 but not table 2
UNION ALL records in table 2 but not table 1
 
Upvote 0
Yes, I can have a look at this later: I'm short of time right now.

This old thread has something very similar that can be modified to suit, if you have the time, http://www.mrexcel.com/forum/showthread.php?t=238791

The SQL there in post #37 is below. HTH, Fazza

Code: 
SELECT 'Removed 2Q' AS [Account Change], tbl_1Q.*
FROM {oj tbl_1Q tbl_1Q LEFT OUTER JOIN tbl_2Q tbl_2Q ON tbl_1Q.`Account #` = tbl_2Q.`Account #`}
WHERE (tbl_2Q.`Account #` Is Null)
 
UNION
SELECT 'Added 2Q' AS [Account Change], tbl_2Q.*
FROM {oj tbl_2Q tbl_2Q LEFT OUTER JOIN tbl_1Q tbl_1Q ON tbl_2Q.`Account #` = tbl_1Q.`Account #`}
WHERE (tbl_1Q.`Account #` Is Null)
 
UNION
SELECT 'Risk Change 2Q' AS [Account Change], tbl_2Q.*
FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q
WHERE tbl_2Q.`Account #` = tbl_1Q.`Account #` AND tbl_2Q.`Risk Grade` <> tbl_1Q.`Risk Grade`
 
Upvote 0
Hi,

Code below using a query table. Modify for your file paths.

The query table can be manually created, so no VBA is required, just use the same SQL as below.

HTH, Fazza

Code: 
Sub using_query_table()
 
  Const strFILE1 As String = "D:\test\File 1"
  Const strPATH_1 As String = "D:\test"
  Const strFILE2 As String = "D:\test\File 2"
 
  Dim strConn As String
  Dim strSQL As String
  Dim wbk As Workbook
 
  strConn = "ODBC;DSN=Excel Files;DBQ=" & strFILE1 & ".xls;DefaultDir=" & _
      strPATH_1 & ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;"
 
  strSQL = Join$(Array( _
      "SELECT A.Identifier, A.`Shares 1`, B.`Shares 2`, A.`Shares 1` - B.`Shares 2` AS [Difference]", _
      "FROM [Sheet1$] A, `" & strFILE2 & "`.[Sheet1$] B", _
      "WHERE A.Identifier = B.Identifier", _
      "UNION", _
      "SELECT A.Identifier, A.`Shares 1`, 0 as [Shares 2], A.`Shares 1` AS [Difference]", _
      "FROM {oj [Sheet1$] A LEFT OUTER JOIN `" & strFILE2 & "`.[Sheet1$] B ON A.Identifier = B.Identifier}", _
      "WHERE B.Identifier Is Null", _
      "UNION", _
      "SELECT 0 AS [Identifier], 0 AS [Shares 1], B.`Shares 2`, - B.`Shares 2` AS [Difference]", _
      "FROM {oj `" & strFILE2 & "`.[Sheet1$] B LEFT OUTER JOIN [Sheet1$] A ON A.Identifier = B.Identifier}", _
      "WHERE A.Identifier Is Null"), vbCr)
 
  Set wbk = Workbooks.Add(template:=xlWBATWorksheet)
  With wbk.Worksheets(1)
    With .QueryTables.Add(Connection:=strConn, Destination:=.Range("A1"), Sql:=strSQL)
      .Refresh BackgroundQuery:=False
    End With
  End With
  Set wbk = Nothing
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Picture won't paste in cell overlay
Replies
0
Views
96
Cerka
C
E
  • Question
Adding a custom message.
Replies
1
Views
455
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
V
  • Question
Issue with scraping with VBA
Replies
1
Views
533
John_w
J
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
399
Alex Blakenburg
A

Forum statistics

Threads
1,215,824
Messages
6,127,077
Members
449,358
Latest member
Snowinx

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top