Select Union query in Excel

[mikeb]

Hi, I have a WB that contains x number of sheets. There are multiple sheets
that that are called Open (Open1, Open2, etc) and the same for sheets that
are called Closed (we use Excel 2003, limited to 65k rows, tha is why we use
multiple sheets for open and closed transactions). Each sheet has a SecurID
column that is supposed to be unique. I need to run a query that will check
for duplicate SecurID's and give me the ID and # of duplicates. Its a select
union query between Open and Closed. IMPORTANT, the query need to run on the
curent workbook that is in memory and not a saved version of the file on the
network. Also, what type of a ADO connction string would I use to connect to
the current open WB.

Thanks,
Mike

 

[Bernie Deitrick]

Mike,

I don't have an answer for you - there are techniques within Excel to get an
answer - but you are using the wrong application: any database application
would be preferable to Excel with that number of records.

Bernie
MS Excel MVP

 

[mikeb]

Hi Bernie, the reason for all of this is validation. We currently do what
you and others suggest. We first upload this data to Access tables and then
run a series of validation queries. What we are thinking about is running
validation in Excel before we import to Access, that way if something is
wrong we can fix it before it is in Access. So I wanted to try and convert a
query to select columns in worksheets instead of fields in a table. If you
know of any code where Excel is the data source (with specifiec queries, not
Select * from sheets1) that you can point me to, I would be very grateful.

Thanks again,
Mike