Stock inventory - count unique sequences from barcode

[ChemistryBMS]

Hi,

I am looking to obtain an overall itemised count from a delivery taken from the scanned barcodes. The first image is the full inventory catalogue at where the items are looked up against.

Stocklist snapshot.xlsx
	A	B	C	D	E	F	G	H
1	Item	Part number	Tests Per Kit ( tests x cartridge )
2	Amylase reagent	4T8520	640 (160x4)		The full inventory has a catalogue of all the items. Where the sizes are different, the test per kit is slightly different, even though is shares the same item name
3	Pancreatic Amylase reagent	1R0422	200 (100X2)
4	Free PSA reagent	7P9320	2 x 100
5	Free PSA reagent	7P9330	2 x 500
6	TSH reagent	7P4820	2 x 100
7	TSH reagent	7P4830	2 x 600
Full inventory

The second image shows the Receiving tab, where the user will scan a barcode to column A and the remaining columns will auto-populate.

Stocklist snapshot.xlsx
	A		C	D		F	G	H	I	J	K	L
1	Barcode		Item	Lot number		Expiry	Tests Per Kit ( tests x cartridge )
2	0100380740131142172408151051092UD0524007P4820		TSH reagent	51092UD05		15/08/2024	2 x 100	Column A (Barcode) is the scanning of a 2D barcode which present the uniform characters. From this we can determine item, lot and expiry. In the example, we have x2 different lot numbers of TSH reagent.
3	0100380740130329172405201053077FN0024007P9320		Free PSA reagent	53077FN00		20/05/2024	2 x 100
4	0100380740131142172408151051092UD0524007P4820		TSH reagent	51092UD05		15/08/2024	2 x 100
5	0100380740154196172406301030578Y60024001R0422		Pancreatic Amylase reagent	30578Y600		30/06/2024	200 (100X2)
6	0100380740155612172405071049600UD0024004T8520		Amylase reagent	49600UD00		07/05/2024	640 (160x4)
7	0100380740131142172412151051092UD0624007P4820		TSH reagent	51092UD06		15/12/2024	2 x 100
8	0100380740131142172412151051092UD0624007P4820		TSH reagent	51092UD06		15/12/2024	2 x 100
9
10
11
12
Receiving

Cell Formulas
Range	Formula
C2:C12	C2	=IFERROR(INDEX('Full inventory'!A:A,(MATCH([@[Item (extract)]],'Full inventory'!B:B,0))),"")
D2:D12	D2	=MID(A2,27,9)
F2:F12	F2	=IFERROR(DATE(2000+LEFT(E2,2), MID(E2,3,2), RIGHT(E2,2)),"")
G2:G12	G2	=IFERROR(INDEX('Full inventory'!C:C,(MATCH([@Item],'Full inventory'!A:A,0))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F998	Expression	=AND(F2-TODAY()>=1, F2-TODAY()<100)	text	NO

In the third and main image, I am trying to achieve the overall counts of itemised lots.

Stocklist snapshot.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Lot 1			Lot 2			Lot 3			Lot 4
2	Item	Part number	Quantity	Lot number	Expiry	Quantity2	Lot number2	Expiry2	Quantity3	Lot number3	Expiry3	Quantity4	Lot number4	Expiry4
3	Amylase reagent	4T8520	1	49600UD00	07/05/2024
4	Pancreatic Amylase reagent	1R0422	1	30578Y600	30/06/2024
5	Free PSA reagent	7P9320	1	53077FN00	20/05/2024
6	TSH reagent	7P4830	2	51092UD05	15/08/2024	2	51092UD06	15/12/2024
7
8	The aim is to obtain the overall numbers of each items lot numbers from the 'Receiving' tab. Under columns C-E (Lot 1), the aim is: - (D:D) Lot number = return the first lot number listed from the 'Receiving' tab - (C:C) Quantity = count the amount of times the adjacent cell (part number) appears in the 'Receiving' tab with the occurence of the the lot number in the adjacent cell D - (E:E) Expiry = return the expiry of the specific lot Under columns F-H (Lot 2), the aims are similar to Lot 1, except this will return the second existing lot number from the 'Receiving' tab. This will be the same for lot 3 and lot 4.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Summary

I know it may be a few nested functions but limitations of work software is MS Excel 2013.

Thanks, ChemistryBMS