Help Index Match

[Joeun]

Hi guys,

I was trying to work out an Index Match for my summary.

The problem is whenever the balance column K is 0, then the rest of the below will become disappear.

What I wanted is when that particular row balance is 0, that roll will not show and the row below will move upward.

Thanks for any suggestions.

Stock.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	DATE	LOT NO.	IN KG	OUT KG	BALANCE										Example
3	26/03/20	18S0165W	23,529.00		23,529.00			Summary						Summary
4	30/03/20	18S0165W		1,650.00	21,879.00			Lot No	In	Out	Balance			Lot No	In	Out	Balance
5	03/04/20	18S0165W		3,300.00	18,579.00			18S0165W	85394	84113	1281			18S0165W	85394	85394	0	(when balance is 0)
6	07/04/20	18S0165W		3,300.00	15,279.00			18S0218W	16467	15843	624			18S0218W	16467	15843	624
7	09/04/20	18S0165W		3,300.00	11,979.00			18S0282W	16929	16907	22			18S0282W	16929	16907	22
8	15/04/20	18S0165W		3,300.00	8,679.00			18S0300W	31029	28520	2509			18S0300W	31029	28520	2509
9	30/04/20	18S0165W	23,529.00		32,208.00				0	0	0				0	0	0
10	27/05/20	18S0165W		3,300.00	28,908.00				0	0	0				0	0	0
11	05/06/20	18S0165W		1,980.00	26,928.00				0	0	0				0	0	0
12	09/06/20	18S0165W		726.00	26,202.00
13	11/06/20	18S0165W		1,320.00	24,882.00
14	27/07/20	18S0165W	1,377.00		26,259.00										Targeted Result
15	11/08/20	18S0165W		816.00	25,443.00									Summary
16	25/08/20	18S0165W		660.00	24,783.00									Lot No	In	Out	Balance
17	07/09/20	18S0165W		2,244.00	22,539.00									18S0218W	16467	15843	624
18	24/09/20	18S0165W		1,980.00	20,559.00									18S0282W	16929	16907	22
19	02/10/20	18S0165W		1,089.00	19,470.00									18S0300W	31029	28520	2509
20	05/10/20	18S0165W		1,320.00	18,150.00										0	0	0
21	19/10/20	18S0165W		858.00	17,292.00										0	0	0
22	11/11/20	18S0165W		693.00	16,599.00										0	0	0
23	24/11/20	18S0165W		660.00	15,939.00										0	0	0
24	08/12/20	18S0165W		1,320.00	14,619.00
25	15/12/20	18S0165W		4,620.00	9,999.00
26	05/01/21	18S0165W		6,600.00	3,399.00
27	04/02/21	18S0165W		3,399.00	-
28	08/03/21	18S0218W	16,467.00		16,467.00
29	08/03/21	18S0218W		1,980.00	14,487.00
30	10/03/21	18S0218W		528.00	13,959.00
31	11/03/21	18S0218W		726.00	13,233.00
32	01/04/21	18S0218W		1,320.00	11,913.00
33	07/04/21	18S0218W		1,386.00	10,527.00
34	06/04/21	18S0218W		660.00	9,867.00
35	13/04/21	18S0218W		1,188.00	8,679.00
36	15/04/21	18S0218W		1,320.00	7,359.00
37	16/04/21	18S0218W		1,320.00	6,039.00
38	21/04/21	18S0218W		660.00	5,379.00
39	22/04/21	18S0218W		726.00	4,653.00
40	05/06/21	18S0282W	16,929.00		16,929.00
41	15/07/21	18S0218W		2,970.00	1,683.00
42	21/07/21	18S0218W		396.00	1,287.00
43	24/07/21	18S0218W		363.00	924.00
44	30/07/21	18S0218W		300.00	624.00
45	30/07/21	18S0282W		396.00	16,533.00
46	23/08/21	18S0282W		1,980.00	14,553.00
47	22/09/21	18S0282W		2,112.00	12,441.00
48	24/09/21	18S0282W		1,584.00	10,857.00
49	18/10/21	18S0282W		528.00	10,329.00
50	19/10/21	18S0282W		792.00	9,537.00
51	20/10/21	18S0282W		1,848.00	7,689.00
52	27/10/21	18S0282W		1,056.00	6,633.00
53	01/11/21	18S0282W		6,303.00	330.00
54	25/11/21	18S0165W	5,907.00		5,907.00
55	29/11/21	18S0165W		1,452.00	4,455.00
56	29/11/21	18S0282W		297.00	33.00
57	29/11/21	18S0165W		3,300.00	1,155.00
58	30/11/21	18S0282W		11.00	22.00
59	06/12/21	18S0165W		1,155.00	-
60	20/12/21	18S0165W	37.00		37.00
61	24/12/21	18S0300W	23,529.00		23,529.00
62	29/12/21	18S0300W		3,300.00	20,229.00
63	31/12/21	18S0300W		891.00	19,338.00
64	04/01/22	18S0300W		6,600.00	12,738.00
65	18/01/22	18S0300W	7,500.00		20,238.00
66	24/01/22	18S0300W		3,300.00	16,938.00
67	11/02/22	18S0300W		1,980.00	14,958.00
68	11/02/22	18S0165W	23,529.00		23,566.00
69	16/02/22	18S0300W		1,056.00	13,902.00
70	23/02/22	18S0300W		660.00	13,242.00
71	24/02/22	18S0300W		528.00	12,714.00
72	01/07/22	18S0300W		33.00	12,681.00
73	04/07/22	18S0300W		5,148.00	7,533.00
74	03/08/22	18S0165W		2,244.00	21,322.00
75	08/08/22	18S0165W		2,508.00	18,814.00
76	24/08/22	18S0165W		726.00	18,088.00
77	03/09/22	18S0165W		1,980.00	16,108.00
78	07/09/22	18S0165W		5,016.00	11,092.00
79	07/09/22	18S0165W		165.00	10,927.00
80	12/09/22	18S0165W		7,491.00	3,436.00
81	14/09/22	18S0165W		2,145.00	1,291.00
82	11/10/22	18S0165W		1,254.00	37.00
83	28/10/22	18S0165W	226.00		263.00
84	19/04/23	18S0165W		263.00	-
85	19/04/23	18S0300W		8.00	7,525.00
86	14/05/23	18S0165W	7,260.00		7,260.00
87	22/05/23	18S0300W		1,456.00	6,069.00
88	18/06/23	18S0300W		3,560.00	2,509.00
89	06/07/23	18S0165W		792.00	6,468.00
90	07/07/23	18S0165W		2,013.00	4,455.00
91	15/08/23	18S0165W		792.00	3,663.00
92	21/08/23	18S0165W		594.00	3,069.00
93	22/08/23	18S0165W		595.00	2,474.00
94	23/08/23	18S0165W		596.00	1,878.00
95	24/08/23	18S0165W		597.00	1,281.00
96	25/08/23	18S0165W			1,281.00
97
Sheet3

Cell Formulas
Range	Formula
H5:H11	H5	=IF(SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($H$4:H4,B3:B107),),0)),$C$3:$C$107)-SUMIF($B$3:$B$107,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($H$4:H4,B3:B107),),0)),$D$3:$D$107)>0,INDEX(B3:B107,MATCH(0,INDEX(COUNTIF($H$4:H4,B3:B107),),0)),"")
I5:I11	I5	=SUMIF($B$3:$B$107,H5,$C$3:$C$107)
J5:J11	J5	=SUMIF($B$3:$B$107,H5,$D$3:$D$107)
K5:K11	K5	=I5-J5
E3:E96	E3	=IFNA(LOOKUP(2,1/($B$1:B2=B3),$E$1:E2),0)+C3-D3

 

[Muhammad_Usman]

1235.xlsm
	B	C	D	E	F	G	H	I	J
1
2	LOT NO.	IN KG	OUT KG	BALANCE		LOT NO.	IN	Out	Difference
3	18S0165W	23529		23529		18S0218W	16467	15843	624
4	18S0165W		1650	21879		18S0282W	16929	16907	22
5	18S0165W		3300	18579		18S0300W	31029	28520	2509
6	18S0165W		3300	15279		18S0165W	85394	84113	1281
7	18S0165W		3300	11979
8	18S0165W		3300	8679
9	18S0165W	23529		32208
10	18S0165W		3300	28908
11	18S0165W		1980	26928
12	18S0165W		726	26202
13	18S0165W		1320	24882
14	18S0165W	1377		26259
15	18S0165W		816	25443
16	18S0165W		660	24783
17	18S0165W		2244	22539
18	18S0165W		1980	20559
19	18S0165W		1089	19470
20	18S0165W		1320	18150
21	18S0165W		858	17292
22	18S0165W		693	16599
23	18S0165W		660	15939
24	18S0165W		1320	14619
25	18S0165W		4620	9999
26	18S0165W		6600	3399
27	18S0165W		3399	0
28	18S0218W	16467		16467
29	18S0218W		1980	14487
30	18S0218W		528	13959
31	18S0218W		726	13233
32	18S0218W		1320	11913
33	18S0218W		1386	10527
34	18S0218W		660	9867
35	18S0218W		1188	8679
36	18S0218W		1320	7359
37	18S0218W		1320	6039
38	18S0218W		660	5379
39	18S0218W		726	4653
40	18S0282W	16929		16929
41	18S0218W		2970	1683
42	18S0218W		396	1287
43	18S0218W		363	924
44	18S0218W		300	624
45	18S0282W		396	16533
46	18S0282W		1980	14553
47	18S0282W		2112	12441
48	18S0282W		1584	10857
49	18S0282W		528	10329
50	18S0282W		792	9537
51	18S0282W		1848	7689
52	18S0282W		1056	6633
53	18S0282W		6303	330
54	18S0165W	5907		5907
55	18S0165W		1452	4455
56	18S0282W		297	33
57	18S0165W		3300	1155
58	18S0282W		11	22
59	18S0165W		1155	0
60	18S0165W	37		37
61	18S0300W	23529		23529
62	18S0300W		3300	20229
63	18S0300W		891	19338
64	18S0300W		6600	12738
65	18S0300W	7500		20238
66	18S0300W		3300	16938
67	18S0300W		1980	14958
68	18S0165W	23529		23566
69	18S0300W		1056	13902
70	18S0300W		660	13242
71	18S0300W		528	12714
72	18S0300W		33	12681
73	18S0300W		5148	7533
74	18S0165W		2244	21322
75	18S0165W		2508	18814
76	18S0165W		726	18088
77	18S0165W		1980	16108
78	18S0165W		5016	11092
79	18S0165W		165	10927
80	18S0165W		7491	3436
81	18S0165W		2145	1291
82	18S0165W		1254	37
83	18S0165W	226		263
84	18S0165W		263	0
85	18S0300W		8	7525
86	18S0165W	7260		7260
87	18S0300W		1456	6069
88	18S0300W		3560	2509
89	18S0165W		792	6468
90	18S0165W		2013	4455
91	18S0165W		792	3663
92	18S0165W		594	3069
93	18S0165W		595	2474
94	18S0165W		596	1878
95	18S0165W		597	1281
96	18S0165W			1281
Sheet10

Cell Formulas
Range	Formula
D4	D4	=1650
E3:E96	E3	=IFNA(LOOKUP(2,1/($B$1:B2=B3),$E$1:E2),0)+C3-D3

 

[Muhammad_Usman]

This can b done by simply qwery query step as i send in pic

 

[KRice]

First, I wanted to confirm that you do not use Excel 365? Because if you do, some relatively easy formulas would lead to a much more direct solution.

There are a couple of issues with the formula that is intended to return unique lot numbers:
1. Some of the ranges need to be locked so that they reference the entire (and correct) full range of data (currently several ranges are not locked).
2. The formula will return a blank if the Balance is <=0...which I know is intended, but within the formula is COUNTIF($H$4:H4,B3:B107). This looks above at the list of unique Lot Numbers that have already been found. And because the logic in the first part of the formula (with the SUMIFs) has potentially resulted in a unique Lot Number being ignored and replaced by a blank (""), COUNTIF($H$4:H4,B3:B107) cannot consider that the unique lot number has already been found and should be ignored; therefore the formula attempts to return the same unique Lot Number...and the SUMIFs logic will continually return a blank. And this happens row after row in the Summary table.

I would recommend forming some helper columns (they can be hidden). One helper contains the unique Lot Numbers. Another contains unique Lot Numbers whose final Balance >0. One other Helper cell is used to form dynamic ranges so that the entire range of your source data is considered, without making the range any longer than necessary (this suppresses a 0 that would be returned in subsequent steps). Then the final Summary table can be created by referring to the intermediate results in the helper cells. In the sample below, I've forced a 0 balance for lot 18S0165W by inserting 1281 in cell D3, and when that happens, the lot is removed from the summary table and the table collapses to include only those lots whose balance remains positive.

MrExcel_20231030_B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	DATE	LOT NO.	IN KG	OUT KG	BALANCE
3	43916	18S0165W	23529	1281	22248			Summary
4	43920	18S0165W		1650	20598			Lot No	In	Out	Balance	Helper Uniques	Helper PosBal	Lrow
5	43924	18S0165W		3300	17298			18S0218W	16467	15843	624	18S0165W		96
6	43928	18S0165W		3300	13998			18S0282W	16929	16907	22	18S0300W	18S0300W
7	43930	18S0165W		3300	10698			18S0300W	31029	28520	2509	18S0282W	18S0282W
8	43936	18S0165W		3300	7398							18S0218W	18S0218W
9	43951	18S0165W	23529		30927
10	43978	18S0165W		3300	27627
11	43987	18S0165W		1980	25647
12	43991	18S0165W		726	24921
13	43993	18S0165W		1320	23601
14	44039	18S0165W	1377		24978
15	44054	18S0165W		816	24162
16	44068	18S0165W		660	23502
17	44081	18S0165W		2244	21258
18	44098	18S0165W		1980	19278
19	44106	18S0165W		1089	18189
20	44109	18S0165W		1320	16869
Sheet4

Cell Formulas
Range	Formula
H5:H10	H5	=IFERROR(INDEX(M:M,AGGREGATE(14,6,ROW($M$5:$M$10)/--($M$5:$M$10<>""),ROWS(H$4:H4))),"")
I5:I10	I5	=IF(H5="","",SUMIF(B:B,H5,C:C))
J5:J10	J5	=IF(H5="","",SUMIF(B:B,H5,D:D))
K5:K10	K5	=IF(H5="","",I5-J5)
L5:L10	L5	=IFNA(LOOKUP(2,1/(COUNTIF(L$4:L4,$B$3:INDEX($B:$B,$N$5))=0),$B$3:INDEX($B:$B,$N$5)),"")
M5:M10	M5	=IF(SUMIF(B:B,L5,C:C)-SUMIF(B:B,L5,D:D)>0,L5,"")
N5	N5	=LOOKUP(2,1/--(B:B<>""),ROW(B:B))
E3:E20	E3	=IFNA(LOOKUP(2,1/($B$1:B2=B3),$E$1:E2),0)+C3-D3