Thread: How to make this data easy to analyse?

Hi,

I have a problem trying to analyse a big inventory data from 2 different spreadsheets.

Here's what my problem is:

Data 1 (for On hand stock)

Location ID Item Cost Available On order
2 TV 200 32
2 Radio 40 12
2 Vacuum Cleaner 45 34
2 Refrigerator 380 54
2 Blender 25 36
2 Microwawe 190 11
23 TV 200 32
23 Radio 40 12
23 Vacuum Cleaner 45 34
23 Refrigerator 380 54
23 Blender 25 36
23 Microwawe 190 11
21 TV 200 32
21 Radio 40 12
21 Vacuum Cleaner 45 34
21 Refrigerator 380 54
21 Blender 25 36
21 Microwawe 190 11


Data for items on order

Location ID Item On Order
3 TV 21
3 Microwave 3
2 Radio 2
2 Blender 6
2 Vacuum Cleaner 56
21 TV 67
21 Blender 68
23 Microwave 54
23 TV 32
45 Vacuum Cleaner 2
45 Microwave 23
46 Microwave 49
46 TV 10
46 Blender 22
46 Radio 15


What I want is some function which will give me the data on order in the highlighted section in Data 1. I think vlookup won't work because it has multiple entries with the same value. Also, if there is nothing on order for a particular item at a particular location, it should return 0. I have attached the spreadsheet for better understanding.
I appreciate any help with regards to my question.

Thanks in advance.

Please find attached solution to your problem