VBA Index/Multiple Match criteria

[vhuang]

Hi,
I came across some similar threads but unfortunately have not been able to "crack the code" and keep on getting a #value error.

I am able in excel to write the index-match array formula but unfortunately, my data set is so large that it either crashes my computer or takes 30-40 minutes to process the action.
I'm hoping to find a vba code to alleviate the stress of the processing.

My data set looks a bit like the attached spreadsheet where I have data from the "Data dump" spreadsheet that needs to be re-organized into the "extracted" spreadsheet. If the value from the "data dump" is null, I would like the quantity to show 0. I need to have the quantity showing up below the country headers in columns K&L on the extracted tab.

I've been trying to get the script to work all day with no luck.

any help would be appreciated!

[p45cal]

I suspect a pivot table will be the quickest way (it took less than a minute to create the pivot table), see attached where there is a pivot table on the extracted sheet next to where you wanted the results. Any good?

There are three lines in the results which are totals of more than one row in the source data:
fruit color age from to quantity
apple red 5 africa europe 3
apple red 2 africa europe 4

orange red 3 asia europe 3
orange red 7 asia europe 4

apple red 3 asia europe 3
apple red 3 asia europe 4

These have been totalled in the pivot on one line. I don't know how you wanted to handle these in your table.