comparing distinct data

Hi,

I was wondering if there was somebody who could help me with what I hope is
a quick problem.

Basically, I have two worksheets.
In one worksheet, I have a column full of unique id numbers. And related to
that specific id number, I have rows of information with info like street
address and name.

In another worksheet, I also have a column full of unique id numbers. Some
of these id numbers match up with the id numbers that are in the 1st
worksheet. But there's many extraneous records that are irrelevant. In this
worksheet, I have rows of information with info on City and State.

So basically, I was wondering, is there any way I can match up the records
with id numbers that match up on worksheet 1 & 2 and discard the records on
worksheet 2 that don't have a corresponding record in worksheet 1? aka, I'd
like to match up the street address, name, city and state for onto one
worksheet. There's just too many records for me to do this by hand.

I'm sorry if this was confusing, I'd be more than happy to explain further.
Any and all help would be GREATLY appreciated. Thanks!

Brian

Hi

you can use VLOOKUP to bring the City and State information from Sheet 2 to
Sheet 1

assume on sheet2 your IDs are in column A, City & State in columns B & C -
starting at row 2 and going to row 1000
assume on sheet1 your IDs are in column A and you want City in D and State
in E

on sheet1 the formula in D2 would be
=VLOOKUP(A2,Sheet2!$A$2:$C$1000,2,0)
on sheet1 the formula in E2 would be
=VLOOKUP(A2,Sheet2!$A$2:$C$1000,3,0)

you could then select these two cells and fill down (select both cells and
then move your mouse over the bottom right hand corner of E2 and when you
see a + double click) the rest of the ID numbers on Sheet 1 to bring the
data across. You could then, if you want, select columns D & E of sheet 1
and use copy / edit, paste special - values to change the formula into the
result and then sheet2 could be deleted.

hope this helps
Cheers
JulieD


"junkit132000" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I was wondering if there was somebody who could help me with what I hope
> is
> a quick problem.
>
> Basically, I have two worksheets.
> In one worksheet, I have a column full of unique id numbers. And related
> to
> that specific id number, I have rows of information with info like street
> address and name.
>
> In another worksheet, I also have a column full of unique id numbers. Some
> of these id numbers match up with the id numbers that are in the 1st
> worksheet. But there's many extraneous records that are irrelevant. In
> this
> worksheet, I have rows of information with info on City and State.
>
> So basically, I was wondering, is there any way I can match up the records
> with id numbers that match up on worksheet 1 & 2 and discard the records
> on
> worksheet 2 that don't have a corresponding record in worksheet 1? aka,
> I'd
> like to match up the street address, name, city and state for onto one
> worksheet. There's just too many records for me to do this by hand.
>
> I'm sorry if this was confusing, I'd be more than happy to explain
> further.
> Any and all help would be GREATLY appreciated. Thanks!
>
> Brian

1. Open the workbook; if you have made any changes, save it.
2. My assumptions: your first sheet is called Sheet1, the second is called
Sheet2 (the one from which data is discarded).
3. Both sheets contain data from Row 1 down AND row 1 contains field names,
one of the common names is ID.

Now try this code:

Sub xx()
cnn = "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=" &
ThisWorkbook.FullName
Sql = "SELECT * FROM [SHEET2$] WHERE ID IN(SELECT ID FROM [SHEET1$])"
Set adors = CreateObject("ADODB.RecordSet")
adors.Open Sql, cnn
If Not adors.EOF Then
ActiveWorkbook.Worksheets.Add
ActiveSheet.Range("A2").CopyFromRecordset adors
End If
For i = 0 To adors.Fields.Count - 1
ActiveSheet.Cells(1, i + 1).Value = adors.Fields(i).Name
Next
adors.Close
Set adors=NOTHING
End Sub

Copy the code to the ThisWorkbook module & run it. IT will add a new sheet
into which it will add the data that you want to keep.
If this does what you want or it can be adapted to do so, you can delete
Sheet2 or make the CopyFromRecordSet method apply to Sheet2.


"junkit132000" wrote:

> Hi,
>
> I was wondering if there was somebody who could help me with what I hope is
> a quick problem.
>
> Basically, I have two worksheets.
> In one worksheet, I have a column full of unique id numbers. And related to
> that specific id number, I have rows of information with info like street
> address and name.
>
> In another worksheet, I also have a column full of unique id numbers. Some
> of these id numbers match up with the id numbers that are in the 1st
> worksheet. But there's many extraneous records that are irrelevant. In this
> worksheet, I have rows of information with info on City and State.
>
> So basically, I was wondering, is there any way I can match up the records
> with id numbers that match up on worksheet 1 & 2 and discard the records on
> worksheet 2 that don't have a corresponding record in worksheet 1? aka, I'd
> like to match up the street address, name, city and state for onto one
> worksheet. There's just too many records for me to do this by hand.
>
> I'm sorry if this was confusing, I'd be more than happy to explain further.
> Any and all help would be GREATLY appreciated. Thanks!
>
> Brian

AA2e72E, A few points:

1) Your code uses ADO to query an open workbook. This is a bad idea due
to an ADO bug:

BUG: Memory leak occurs when you query an open Excel worksheet by using
ActiveX Data Objects (ADO)

http://support.microsoft.com/default...;en-us;Q319998

2) The Microsoft OLE DB provider for Jet 4.0 is the provider of choice
for ADO enthusiast in this group, so your connection string could be
re-written as:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Tempo\db.xls;
Extended Properties=Excel 8.0

Perhaps you were using the odbc driver and respective provider in an
attempt to circumvent the memory leak bug? This does not work because
the bug is in ADO and is not limited to particular drivers and
providers. I've tested with the OLE DB providers for Jet 4.0, Jet 3.51
and odbc and all caused the memory leak to occur. The only realistic
work around is to use a closed copy of the workbook and, contrary to
Method 1 in the above MS article, the copy should not be made using
SELECT..INTO because this in itself involves querying the open
workbook.

3) The usual approach is to JOIN the sheets/tables. Here's is a
suggested alternative using a JOIN:

SELECT T2.*
FROM [Sheet2$] AS T2
INNER JOIN [Sheet1$] AS T1
ON T2.ID = T1.ID;

FWIW your subquery construct should be equivalent in terms of
performance; in fact, my experience of Jet suggests yours may even run
a little faster than mine. I post mine merely because it is the more
common construct.

4) The data/header row does not need to start in row 1 when using
[Sheet1$] as the table name. Jet will determine the table confines
using the UsedRange (but at a much lower level than VBA does).

Jamie.

--