How do I merge the data from multiple worksheets into a single worksheet?
I have a spreadsheet which contains multiple worksheets, 2 of which contain data of an identical structure. Each of the 2 worksheets contain ZIP CODE, STATE, CITY, and a series of 3 business related indicators (FLAG A, B, and C).
Worksheet A contains static data for the approximate 40,000 zip codes that are outside of our business zone.
Worksheet B contains the same data for the approximate 4,000 zip codes that are within our business zone. The cells for this sheet are dynamically linked to a separate spreadsheet which serves as a sort of central data repository.
I can't store all 44,000 rows in the data repository, as the update of a 1/4 million cells causes the update to fail via resources.
I need to sort and merge the data from worksheets A and B into worksheet C, so that I have a single data source for performing array functions (specifically a VLookup) from a census table, which can contain ZIPs from either of the 2 worksheets.
Worksheet A contains static data for the approximate 40,000 zip codes that are outside of our business zone.
Worksheet B contains the same data for the approximate 4,000 zip codes that are within our business zone. The cells for this sheet are dynamically linked to a separate spreadsheet which serves as a sort of central data repository.
I can't store all 44,000 rows in the data repository, as the update of a 1/4 million cells causes the update to fail via resources.
I need to sort and merge the data from worksheets A and B into worksheet C, so that I have a single data source for performing array functions (specifically a VLookup) from a census table, which can contain ZIPs from either of the 2 worksheets.
ASKER
Hi. Thanks for the response.
This looks like it's on the right track, but I need the syntax for including the 2 different worksheet ranges as my input for the wizard's 'step 2' (input for the pivot table).
My ranges are: 'All ZipsNonBusiness'!$A$1:$G$3
and: 'All ZipsBusiness'!$A$1:$G$4741
Thanks for your help.
This looks like it's on the right track, but I need the syntax for including the 2 different worksheet ranges as my input for the wizard's 'step 2' (input for the pivot table).
My ranges are: 'All ZipsNonBusiness'!$A$1:$G$3
and: 'All ZipsBusiness'!$A$1:$G$4741
Thanks for your help.
ASKER
Hi again.
I figured out how to use the multiple input sources, however I receive the following error:
"A field in your source data has more unique items than can be used in a PivotTable report. MicroSoft Excel may not be able to create the report, or may create the report without the data from this field."
No report was created.
I figured out how to use the multiple input sources, however I receive the following error:
"A field in your source data has more unique items than can be used in a PivotTable report. MicroSoft Excel may not be able to create the report, or may create the report without the data from this field."
No report was created.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This solution resolved my issue. Additionally, the code is re-usable for addressing any similar updates. It was very well-explained and easy to follow and understand.
Thanks Brian!
Thanks Brian!
Many thanks, FlyingElvii!
Your limits in 2002 are 65535 for rows and, in 2007 or 2010, over 1,048,000. Trial versions are availabe directly from the Microsoft web pages. But, there are many reasons not to upgrade.