Cross-referencing two spreadsheets

[dance]

Hi

I have an interesting question which I think would have to be solved through the use of a combination of Excel functions rather than just one magic formula, but I might be wrong on that. It would be nice to be wrong about that.

Basically I order thrice a week from a certain supplier to fill the shelves of my shop.

They make available to me two spreadsheet dumps: an orders spreadsheet, with the following columns:-

order ref	date	unique product code	description	qty
9262	03/07/2013	622642	Fabric Dye	4

and an invoices spreadsheet with the following columns:-

invoice ref	date	unique product code	description	qty
122551	04/07/2013	622642	Fabric Dye	4

Basically I can see the potential here to find out what we've ordered but that which they haven't sent which would be so handy.

I have tried to vlookup() produce code against product code, this kind of works but below are some example drawbacks:-

1) no idea if I've ordered it ten times and they've only not sent it once, or if I've ordered it ten times and they've not sent it nine times.
2) no idea of dates - i.e. ordered this in June '13, October '13 and February '14 and they sent it in February '14 only.

Any pointers/help would be much appreciated.

THANK YOU

[rcm]

in their invoices there is no reference to your order number?

[dance]

There isn't. But it doesn't matter if the formula isn't able to prove a link between an order and an invoice - it would be quite sufficient for it to say

product	description	ordered	sent
327333	candles	5	4

[rcm]

run the macro in summary, hoping this will be helpful

[dance]

Thank you, thank you, thank you.

[rcm]

just make shure that all workbooks are open. Mark it as solved