excel formula / gadget 1

[Brianfree]

Hi i have a customer who places an enquiry using a ref number and a quantity (please see example data below)..

Ref QTY
acb123 10
def456 20
ghi789 30


I need to match these ref numbers to data in excel and display all the oems and qty's we have available..

Our Stock data format looks like this...

OEM Ref QTY
123456789 acb123 5
234567890 acb123 5
345678901 acb123 2
456789012 acb123 0
567890123 acb123 8
678901234 acb123 3
789012345 def456 1
890123456 def456 67
901234567 ghi789 0

I would like to display the result in a new worsh sheet like...

Cust Ref OEM QTY Available
acb123 123456789 5
acb123 234567890 5
acb123 345678901 2
acb123 456789012 0
acb123 567890123 8
acb123 678901234 3
def456 789012345 1
def456 890123456 67
ghi789 901234567 0


Note: There may be refs that we do not hold data for

Please can anyone help? Ive been looking at match/index but not sure how to do this properly..

Many thanks

Brian

 

[SkipVought]

hi,

This can be done using MS Query via Data > Get Externam Data > From Other sources > From Microsoft Query... and drill down to your workbook, join your customer and stock tables to display the desired result on a separate sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Brianfree]

Perfect thanks for your help ;-)