Access/Excel data to compare and sum conditionally
 

Hello,
I have 87000 rows of data in 5 columns in Access. I have 750 rows of
data in 5 columns in MS Excel. This is all postal codes data.
I want to take the data on the Excel workbook and compare it with the
postal codes in Access. Based on that I want to come up with a top 5
most popular postal code and based on the same I want to sum up with
the associated columns of data.

Access MDB
i. Postal Code
ii. First three digits of Postal Code
iii. City Name
iv. Neighbourhood Number
v. Popular City Name

Excel Workbook
i. Data (to be aggregated)
ii. Data (to be aggregated)
iii. Data (to be aggregated)
iv. Data (to be aggregated)
v. Postal Code

Is there a way to do it in Excel with VBScripting? I do not how to go
around this with the limit of 65536 in Excel. Should I import this into
Access and use it there? I have no idea how to use VBScript on this as
well. If anybody out there can help me with this, that would be great.
Regards


--
abhic
------------------------------------------------------------------------
abhic's Profile: http://www.excelforum.com/member.php...o&userid=16392
View this thread: http://www.excelforum.com/showthread...hreadid=384432

Access/Excel data to compare and sum conditionally
 

Abhic

If you want to aggregate the data in Access for only the records in Excel.
(I think that's the request), I would do it in Access via FileGet external
dataLink Tables

This will allow you to permanently link the Excel table. Create a query
using this 'table' and the table in Access. This will, by default, only
show the records from the Excel table. Once you have produced the report,
this could be exported easily back to Excel using ToolsOffice links

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"abhic" wrote in
message ...

Hello,
I have 87000 rows of data in 5 columns in Access. I have 750 rows of
data in 5 columns in MS Excel. This is all postal codes data.
I want to take the data on the Excel workbook and compare it with the
postal codes in Access. Based on that I want to come up with a top 5
most popular postal code and based on the same I want to sum up with
the associated columns of data.

Access MDB
i. Postal Code
ii. First three digits of Postal Code
iii. City Name
iv. Neighbourhood Number
v. Popular City Name

Excel Workbook
i. Data (to be aggregated)
ii. Data (to be aggregated)
iii. Data (to be aggregated)
iv. Data (to be aggregated)
v. Postal Code

Is there a way to do it in Excel with VBScripting? I do not how to go
around this with the limit of 65536 in Excel. Should I import this into
Access and use it there? I have no idea how to use VBScript on this as
well. If anybody out there can help me with this, that would be great.
Regards


--
abhic
------------------------------------------------------------------------
abhic's Profile:
http://www.excelforum.com/member.php...o&userid=16392
View this thread: http://www.excelforum.com/showthread...hreadid=384432