Show all items on the sheet/compared to SQL data?

[afc171]

Hi all,

I have two sheets, one (Despatch-LC-Stock) which pulls stock from external SQL.
On another sheet (ProductList) I have a list of all products.
On Despatch-LC-Stock this will sometimes have multiple products of the same item in it, as some are different batch/lot numbers.

What I would like is a sheet which shows all products with batches and show items which are not in stock with a 0 or none in stock. Not sure how I would do this?!

any help would be great.

4Despatch Consumables V1 (1).xlsm
	B	C	D	E	F	G	H
2	product	long_description	lot_number	bin_number	quantity	inspection_date	expiry_date
3	GEN0023	Azowipes	15278	LC14COSH	8	20/Jun/2022	31/May/2024
4	GEN0023	Azowipes	15852	LC14COSH	19	29/Jul/2022	30/Jun/2024
5	GEN0023	Azowipes	15959	LC14COSH	20	26/Aug/2022	30/Jun/2024
6	PKG0001	BUF BOX 120 X 79 X 60MM	300421	LC/P8	135	30/Apr/2021
7	PKG0001	BUF BOX 120 X 79 X 60MM	260422	LC/P8	1821	26/Apr/2022
8	PKG0002	CARDICE 11.5KG BAG 10MM PELLETS	051022	KD80	3	05/Oct/2022
9	PKG0003	BUF BOX SLEEVES LIT.BUF	170566KSC	LC/P8	294	27/May/2021
10	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	090719	LC/P8	68	09/Jul/2019
11	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	031019	LC/P8	154	03/Oct/2019
12	PKG0005	ICT KIT SLEEVE LIT.KIT PACK	031019	LC/P8	312	03/Oct/2019
13	PKG0006	MMT/RMT KIT SLEEVE LIT.MMT	169162LC	LC/P8	949	30/Sep/2020
14	PKG0007	MED DRY ICE BOX 283X283X259MM	041022	LC/P8	60	04/Oct/2022
15	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	170367KSC	LC/P8	14	22/Apr/2021
16	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	211021	LC/P8	100	21/Oct/2021
17	PKG0009	DOUBLE WALL BROWN BOX 305X305X305MM L49	167212KSC	LC/P8	71	01/Oct/2019
18	PKG0010	A4 PLAIN DOC ENCLOSED WALLETS 1000/BOX	150720	LC/P8	3000	15/Jul/2020
Despatch-LC-Stock

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:I70	Expression	=ROW(B3)=HighlightStock	text	NO

4Despatch Consumables V1 (1).xlsm
	A	B	C	D
2	product	long_description	stock unit	Book Out QTY
3	GEN0023	Azowipes	EACH	1 EACH
4	GEN0030	Absorbent Spill Mat	EACH	1 EACH
5	PKG0001	BUF BOX 120 X 79 X 60MM	EACH	1 EACH
6	PKG0002	CARDICE 11.5KG BAG 10MM PELLETS	EACH	1 EACH
7	PKG0003	BUF BOX SLEEVES LIT.BUF	EACH	1 EACH
8	PKG0004	BOVINE KIT SLEEVE LIT.KIT BOV	EACH	1 EACH
9	PKG0005	ICT KIT SLEEVE LIT.KIT PACK	EACH	1 EACH
10	PKG0006	MMT/RMT KIT SLEEVE LIT.MMT	EACH	1 EACH
11	PKG0007	MED DRY ICE BOX 283X283X259MM	EACH	1 EACH
12	PKG0008	SINGLE WALL BROWN BOX 305X228X130MM L25	EACH	1 EACH
13	PKG0009	DOUBLE WALL BROWN BOX 305X305X305MM L49	EACH	1 EACH
14	PKG0010	A4 PLAIN DOC ENCLOSED WALLETS 1000/BOX	EACH	1 (BOX 1000)
ProductList

 

[Skovgaard]

Do you mean something like below?
I have created this is PowerQuery by merging both tables, keeping all records from table2.

/Skovgaard

 

[afc171]

Hi Skovgaard
Yes looks good as it shows items not in stock. I created something similar but how to merge both tables?
If i added a manual column on the end would it still align if something came into stock?

Thank you

 

[Skovgaard]

Hi Skovgaard
Yes looks good as it shows items not in stock. I created something similar but how to merge both tables?
If i added a manual column on the end would it still align if something came into stock?

Thank you

See below picture, how I merged the two tables.
Does this help you?

/Skovgaard

 

[afc171]

I don't think I have that menu as we are on Office 2013

 

[Skovgaard]

I don't think I have that menu as we are on Office 2013

Sorry, I didn't pay attention to your version.
I can't think of another way, than I already tried with merging - Perhaps other users have a solution.

Power Query is available as an add-in for 2013, see below site

Power Query data sources in Excel versions - Microsoft Support

Learn more about which Get & Transform (Power Query) features are included in which Microsoft Office SKUs.

support.microsoft.com

/Skovgaard