Lookup & Match - Restaurant Inventory

[djc123]

Hi guys,

I work for a restaurant chain and would like your help on a formula or conditional formatting setup.

We capture our sales through a POS (Point of Sale) system called Lightspeed. Lightspeed is integrated with our Inventory Management System, Marketman. To put it simply, every time a Burger is ordered through Lightspeed, it automatically deducts the bread, patty and salad from the stock in Market Man.

We are finalising the setup in MarketMan and have completed the recipes so that the correct inventory items are deducted when a product is sold in Lightspeed.

What I want is to cross-check the product ID's and product names to ensure that the data being brought into MarketMan from Lightspeed is correct.

The attached Excel shows the product export from Lightspeed (collumns A-B) and product export from MarketMan (collumns D-AC).

The reason why MarketMan has many more collumns is because sometimes one product has multiple product ID's.

I think the best way to match the two would be to do a conditional format on collumn B. The conditional formatting formula would lookup and match any product ID from collumn E to AC, and when it finds a match, it would compare the two names in A and D to see if they are the same. A match would be yellow, a different product name would be red.

Sorry for the long-winded explanation. If you can think of an altnernative way to perform this cross-check I am all ears.

Thank you,

Danny

[alansidman]

Using Power Query which is called Get and Transform in your 2016 version.
1. I brought each table into the PQ Editor.
2. Converted the data type for the Product ID to Text in both
3. Unpivoted the data in the Marketplace Table
4. Joined the two tables with a full outer join on the Product ID
5. Close and loaded the merged (joined) table back to native Excel.