By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

sorting between two spreadsheets

P: n/a
I'm trying to do the following in Excel but am having a really tough
time and am wondering if Access will do it....

I have a database matrix with about a half million rows in it and
about a dozen columns. I have another database with about 1000 rows in
it. Each of these is a long number. What I would like to do is sort
the huge matrix so it only selects/sorts rows that contain ones that
have the same values of the 1000 out of the other spreadsheet. In
other words each row needs to look at the list of 1000 and say yes or
no to whether it contains one of the numbers.

Does this make sense? Will Access do this? How easy is it?

Thanks folks.

Joe

Jan 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Joe:

I don't have a great deal of information regarding your exact setup,
however, it appears that you could do an INNER JOIN between the two
"spreadsheets." An Excel worksheet is limited to about 65536 rows, so I am
assuming your larger data matrix is already in Access. You could link the
smaller data set which I am assuming is in Excel into Access and then
perform the INNER JOIN query.

You can link an Excel spreadsheet into Access through the File menu, Get
External Data, Link Tables option. In the Files of Type drop down, select
Excel and browse to your workbook. You can then create a query from the two
tables joining them on the appropriate field.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<jo***********@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I'm trying to do the following in Excel but am having a really tough
time and am wondering if Access will do it....

I have a database matrix with about a half million rows in it and
about a dozen columns. I have another database with about 1000 rows in
it. Each of these is a long number. What I would like to do is sort
the huge matrix so it only selects/sorts rows that contain ones that
have the same values of the 1000 out of the other spreadsheet. In
other words each row needs to look at the list of 1000 and say yes or
no to whether it contains one of the numbers.

Does this make sense? Will Access do this? How easy is it?

Thanks folks.

Joe
Jan 5 '06 #2

P: n/a

"David Lloyd" <Da***@NoSpamPlease.com> wrote in message
news:1F*******************@bignews3.bellsouth.net. ..
Joe:

I don't have a great deal of information regarding your exact setup,
however, it appears that you could do an INNER JOIN between the two
"spreadsheets." An Excel worksheet is limited to about 65536 rows, so I
am
assuming your larger data matrix is already in Access. You could link the
smaller data set which I am assuming is in Excel into Access and then
perform the INNER JOIN query.

You can link an Excel spreadsheet into Access through the File menu, Get
External Data, Link Tables option. In the Files of Type drop down, select
Excel and browse to your workbook. You can then create a query from the
two
tables joining them on the appropriate field.


Just one comment to add to David's excellent answer. With lots of data, you
can improve performance by making sure that the columns in the Join are
indexed. To index them, they will have to be imported into Access. If
performance is satisfactory linking to Excel, then don't worry about it.

Larry Linson
Microsoft Access MVP
Jan 10 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.