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?