Combining name ranges to create database. Pivot? Consolidate? I've tried and I'm stuck!

[no.reply7]

I have several name ranges with large number of rows. I cannot
aggregate my data into only the 4 columns used in the name ranges as it
would exceed 65536 rows.

Here is what name_range_1 looks like (4 columns)

date | quantity1 | quantity2 | product
4/5/04 | 2 | 4 | lemon
4/6/04 | 3 | 1 | lemon
4/8/04 | 4 | 2 | lemon
4/9/04 | 7 | 3 | lemon
etc

Here is what name_range_2 looks like (4 columns)

date | quantity1 | quantity2 | product
4/4/04 | 2 | 4 | sugar
4/6/04 | 3 | 1 | sugar
4/7/04 | 1 | 6 | sugar
4/9/04 | 5 | 2 | sugar
etc

this is how I need to data

product | lemon | lemon | sugar | sugar
date | quantity1 | quantity2 | quantity1 | quantity2
4/4/04 | | | 2 | 4
4/5/04 | 2 | 4 | |
4/6/04 | 3 | 1 | 3 | 1
4/7/04 | | | 1 | 6
4/8/04 | 4 | 2 | |
4/9/04 | 7 | 3 | 5 | 2

I spent about a day 1/2 googling with no success. I've tried to combine
the data via pivot but I failed. I could do vlookup but with so much
data, it would take forever.

I've read through John Walkenbach's bible but could not find the
answer.

Any thoughts?

 

[RB Smissaert]

It sounds like SQL run on sheet ranges might be your solution.
If it doesn't fit on a sheet you could export to text and run the SQL on
text files.
Look at this posting from 2 Dec 2005 in this newsgroup: Count duplicates in
an array?

RBS

 

[Jim Thomlinson]

If you are beyond 65,536 then you may want to consider storing the data
somewhere else, such as in Access. Assuming that you have Access you can load
your source data into a table and then use a pivot table to retrieve the data
from the table. Doing this you can get aroud the 65,536 limit (Possibly as
high as a million records). In the pivot table wizard the first screen aske
you to select a data source and you just need to select the database type and
then find the database and table.