Excel Export - Remove Certain Rows Only

[PS_Richard]

Hello all,

Apologies now that the subject is a bit vague as trying to type what I want as a summary isn't easy. I have an export that comes from a software package I am unable to edit the formatting so need to do this within Excel.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Product Code	Product Description	Cat	Sales Order	Due Date	Required	In Stock	Shortfall
2		BOS-194	Product Description A1	2	Sales Order	Due Date	Required	In Stock	Shortfall
3		BOS-194	Product Description A1 - Variant 1	2	33228	30/11/2022	1
4		BOS-194	Product Description A1 - Variant 1	2	33480	09/01/2023	1
5		BOS-194	Product Description A1 - Variant 1	2	33481	09/01/2023	1
6		BOS-194	Product Description A1 - Variant 1	2	33482	09/01/2023	1
7		BOS-194	Product Description A1 - Variant 1	2	33483	09/01/2023	1
8		BOS-194	Product Description A1 - Variant 2	2	33836	30/11/2022	1
9		BOS-194	Product Description A1 - Variant 2	2	33841	28/11/2022	1
10		BOS-194	Product Description A1 - Variant 1	2	34262	05/12/2022	3
11		BOS-194	Product Description A1 - Variant 1	2	34331	01/12/2022	1
12		BOS-194	Product Description A1 - Variant 1	2	34481	10/11/2022	1
13		BOS-194	Product Description A1	2	Totals		12	0	-12
14		CLM-027	Product Description A2	1	Sales Order	Due Date	Required	In Stock	Shortfall
15		CLM-027	Product Description A2 - Variant 1	1	34425	10/11/2022	3
16		CLM-027	Product Description A2 - Variant 2	1	34441	10/11/2022	62
17		CLM-027	Product Description A2 - Variant 3	1	34519	10/11/2022	1
18		CLM-027	Product Description A2 - Variant 4	1	34525	10/11/2022	39
19		CLM-027	Product Description A2 - Variant 5	1	34529	10/11/2022	10
20		CLM-027	Product Description A2	1	Totals		115	3073	N/A
21		KML-030	Product Description A3	2	Sales Order	Due Date	Required	In Stock	Shortfall
22		KML-030	Product Description A3 - Variant 1	2	33228	30/11/2022	1
23		KML-030	Product Description A3 - Variant 1	2	33480	09/01/2023	1
24		KML-030	Product Description A3 - Variant 1	2	33481	09/01/2023	1
25		KML-030	Product Description A3 - Variant 1	2	33482	09/01/2023	1
26		KML-030	Product Description A3 - Variant 1	2	33483	09/01/2023	1
27		KML-030	Product Description A3 - Variant 2	2	33836	30/11/2022	1
28		KML-030	Product Description A3 - Variant 2	2	33841	28/11/2022	1
29		KML-030	Product Description A3 - Variant 1	2	34262	05/12/2022	3
30		KML-030	Product Description A3 - Variant 1	2	34331	01/12/2022	1
31		KML-030	Product Description A3 - Variant 1	2	34481	10/11/2022	1
32		KML-030	Product Description A3	2	Totals		12	0	-12
Sheet1

This is an example of the dataset I'm working with but numerous amount of data (over 7000 rows) but this will vary depending on when the report was run. So this is what I am after happening. If in Column I there is a negative amount then it needs to keep that and all the previous fields that have the same product code preceding it, if it shows as N/A it needs to remove those rows.

So based on that logic with the above example it would check down column I, when it spots in I13 that the is a negative amount it will know it needs to keep all the data within B2:I13. It will then move onto the next section and will see in cell I20 that it shows as N/A, it will then need to delete cell range B14:I20 and remove those rows to then proceed to the next where before the rows were removed it would see that the next value in I32 is a negative so would keep those rows.

I hope this makes sense, I'm guessing this will most likely be a VBA script rather than dumping the data in one sheet to have it in another. Any questions you need to find out more information then please let me know. Thanks in advance for any help that can be given.

Thanks, PS_Richard

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Product Code	Product Description	Cat	Sales Order	Due Date	Required	In Stock	Shortfall			Product Code	Product Description	Cat	Sales Order	Due Date	Required	In Stock	Shortfall
2		BOS-194	Product Description A1	2	Sales Order	Due Date	Required	In Stock	Shortfall			BOS-194	Product Description A1	2	Sales Order	Due Date	Required	In Stock	Shortfall
3		BOS-194	Product Description A1 - Variant 1	2	33228	44895	1					BOS-194	Product Description A1 - Variant 1	2	33228	44895	1	0	0
4		BOS-194	Product Description A1 - Variant 1	2	33480	44935	1					BOS-194	Product Description A1 - Variant 1	2	33480	44935	1	0	0
5		BOS-194	Product Description A1 - Variant 1	2	33481	44935	1					BOS-194	Product Description A1 - Variant 1	2	33481	44935	1	0	0
6		BOS-194	Product Description A1 - Variant 1	2	33482	44935	1					BOS-194	Product Description A1 - Variant 1	2	33482	44935	1	0	0
7		BOS-194	Product Description A1 - Variant 1	2	33483	44935	1					BOS-194	Product Description A1 - Variant 1	2	33483	44935	1	0	0
8		BOS-194	Product Description A1 - Variant 2	2	33836	44895	1					BOS-194	Product Description A1 - Variant 2	2	33836	44895	1	0	0
9		BOS-194	Product Description A1 - Variant 2	2	33841	44893	1					BOS-194	Product Description A1 - Variant 2	2	33841	44893	1	0	0
10		BOS-194	Product Description A1 - Variant 1	2	34262	44900	3					BOS-194	Product Description A1 - Variant 1	2	34262	44900	3	0	0
11		BOS-194	Product Description A1 - Variant 1	2	34331	44896	1					BOS-194	Product Description A1 - Variant 1	2	34331	44896	1	0	0
12		BOS-194	Product Description A1 - Variant 1	2	34481	44875	1					BOS-194	Product Description A1 - Variant 1	2	34481	44875	1	0	0
13		BOS-194	Product Description A1	2	Totals		12	0	-12			BOS-194	Product Description A1	2	Totals	0	12	0	-12
14		CLM-027	Product Description A2	1	Sales Order	Due Date	Required	In Stock	Shortfall			KML-030	Product Description A3	2	Sales Order	Due Date	Required	In Stock	Shortfall
15		CLM-027	Product Description A2 - Variant 1	1	34425	44875	3					KML-030	Product Description A3 - Variant 1	2	33228	44895	1	0	0
16		CLM-027	Product Description A2 - Variant 2	1	34441	44875	62					KML-030	Product Description A3 - Variant 1	2	33480	44935	1	0	0
17		CLM-027	Product Description A2 - Variant 3	1	34519	44875	1					KML-030	Product Description A3 - Variant 1	2	33481	44935	1	0	0
18		CLM-027	Product Description A2 - Variant 4	1	34525	44875	39					KML-030	Product Description A3 - Variant 1	2	33482	44935	1	0	0
19		CLM-027	Product Description A2 - Variant 5	1	34529	44875	10					KML-030	Product Description A3 - Variant 1	2	33483	44935	1	0	0
20		CLM-027	Product Description A2	1	Totals		115	3073	N/A			KML-030	Product Description A3 - Variant 2	2	33836	44895	1	0	0
21		KML-030	Product Description A3	2	Sales Order	Due Date	Required	In Stock	Shortfall			KML-030	Product Description A3 - Variant 2	2	33841	44893	1	0	0
22		KML-030	Product Description A3 - Variant 1	2	33228	44895	1					KML-030	Product Description A3 - Variant 1	2	34262	44900	3	0	0
23		KML-030	Product Description A3 - Variant 1	2	33480	44935	1					KML-030	Product Description A3 - Variant 1	2	34331	44896	1	0	0
24		KML-030	Product Description A3 - Variant 1	2	33481	44935	1					KML-030	Product Description A3 - Variant 1	2	34481	44875	1	0	0
25		KML-030	Product Description A3 - Variant 1	2	33482	44935	1					KML-030	Product Description A3	2	Totals	0	12	0	-12
26		KML-030	Product Description A3 - Variant 1	2	33483	44935	1
27		KML-030	Product Description A3 - Variant 2	2	33836	44895	1
28		KML-030	Product Description A3 - Variant 2	2	33841	44893	1
29		KML-030	Product Description A3 - Variant 1	2	34262	44900	3
30		KML-030	Product Description A3 - Variant 1	2	34331	44896	1
31		KML-030	Product Description A3 - Variant 1	2	34481	44875	1
32		KML-030	Product Description A3	2	Totals		12	0	-12
33
Details

Cell Formulas
Range	Formula
L2:S25	L2	=LET(u,UNIQUE(FILTER(B2:B1000,I2:I1000="N/A")),FILTER(B2:I1000,ISNA(MATCH(B2:B1000,u,0))*(B2:B1000<>"")))
Dynamic array formulas.

 

[jdellasala]

This is a job for Power Query! This is the basic table I got from your data:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Product Code	Product Description	Product Item	Cat	Sales Order	Due Date	Required	In Stock	Shortfall	Stock - Required
2	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	34331	12/01/2022	1	0	0	-1
3	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	34481	11/10/2022	1	0	0	-1
4	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	34262	12/05/2022	3	0	0	-3
5	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	33483	01/09/2023	1	0	0	-1
6	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	33480	01/09/2023	1	0	0	-1
7	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	33228	11/30/2022	1	0	0	-1
8	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	33482	01/09/2023	1	0	0	-1
9	BOS-194	Product Description A1	Product Description A1 - Variant 1	2	33481	01/09/2023	1	0	0	-1
10	BOS-194	Product Description A1	Product Description A1 - Variant 2	2	33836	11/30/2022	1	0	0	-1
11	BOS-194	Product Description A1	Product Description A1 - Variant 2	2	33841	11/28/2022	1	0	0	-1
12	CLM-027	Product Description A2	Product Description A2 - Variant 1	1	34425	11/10/2022	3	0	0	-3
13	CLM-027	Product Description A2	Product Description A2 - Variant 2	1	34441	11/10/2022	62	0	0	-62
14	CLM-027	Product Description A2	Product Description A2 - Variant 3	1	34519	11/10/2022	1	0	0	-1
15	CLM-027	Product Description A2	Product Description A2 - Variant 4	1	34525	11/10/2022	39	0	0	-39
16	CLM-027	Product Description A2	Product Description A2 - Variant 5	1	34529	11/10/2022	10	0	0	-10
17	KML-030	Product Description A3	Product Description A3 - Variant 1	2	34262	12/05/2022	3	0	0	-3
18	KML-030	Product Description A3	Product Description A3 - Variant 1	2	33482	01/09/2023	1	0	0	-1
19	KML-030	Product Description A3	Product Description A3 - Variant 1	2	34481	11/10/2022	1	0	0	-1
20	KML-030	Product Description A3	Product Description A3 - Variant 1	2	34331	12/01/2022	1	0	0	-1
21	KML-030	Product Description A3	Product Description A3 - Variant 1	2	33481	01/09/2023	1	0	0	-1
22	KML-030	Product Description A3	Product Description A3 - Variant 1	2	33480	01/09/2023	1	0	0	-1
23	KML-030	Product Description A3	Product Description A3 - Variant 1	2	33228	11/30/2022	1	0	0	-1
24	KML-030	Product Description A3	Product Description A3 - Variant 1	2	33483	01/09/2023	1	0	0	-1
25	KML-030	Product Description A3	Product Description A3 - Variant 2	2	33836	11/30/2022	1	0	0	-1
26	KML-030	Product Description A3	Product Description A3 - Variant 2	2	33841	11/28/2022	1	0	0	-1
27	Count - Total	25					139	0	0	-139
OrdersReport

Cell Formulas
Range	Formula
B27	B27	=SUBTOTAL(103,[Product Description])
G27	G27	=SUBTOTAL(109,[Required])
H27	H27	=SUBTOTAL(109,[In Stock])
I27	I27	=SUBTOTAL(109,[Shortfall])
J27	J27	=SUBTOTAL(109,[Stock - Required])

The Stock - Required column was generated in Power Query. The data provided was kind of weird and hard to interpret what it actually should be.

This is the Pivot Table for that Table:

Book1
	A	B	C	D
3	Row Labels	Sum of Required	Sum of In Stock	Sum of Stock - Required
4	Product Description A1	12	0	-12
5	Product Description A1 - Variant 1	10	0	-10
6	Product Description A1 - Variant 2	2	0	-2
7	Product Description A2	115	0	-115
8	Product Description A2 - Variant 1	3	0	-3
9	Product Description A2 - Variant 2	62	0	-62
10	Product Description A2 - Variant 3	1	0	-1
11	Product Description A2 - Variant 4	39	0	-39
12	Product Description A2 - Variant 5	10	0	-10
13	Product Description A3	12	0	-12
14	Product Description A3 - Variant 1	10	0	-10
15	Product Description A3 - Variant 2	2	0	-2
16	Grand Total	139	0	-139
PT Report

Learning Power Query will seem like a daunting task, but believe me it's worth every minute and designed for just this task. Inventory management I did for over 60,000 Smartphone Users across four Carriers and 100 offices holding inventory would take a day or more to reconcile each carrier, and had I known PQ it would have taken an hour or less.

Mr. E has an excellent YouTube Playlist on the subject: Power Query. Also Mike at the YouTube Channel ExcelIsFun has a full college course complete with before and after Workbooks/Worksheets for each lesson here: Full Advanced Data Analysis & BI Class (MSPTDA). Power Query, Power Pivot, DAX, M Code, Power BI & Excel.

With Power Query, you should be able to take care of this in minutes, and if you hit a speed bump, there's always plenty of help here! Good luck.

 

[PS_Richard]

Thanks @jdellasala, that's something I need to be looking at more into to get better at this but with the current timing I'm trying to get a short term win and using a formula like @Fluff has described is the quicker route for me now.

@Fluff Thanks for the formula, it appears to be working as planned as it's cleaned out over 4k rows that aren't needed leaving around 1k that are. I have hit a snag as two product codes where there is a negative value have failed to come through which is strange. The product codes used are P-EPCA-027 and P-EPCA-071 yet if I take them out and do a separate test with those codes and a few others it works (odd).

As you have done this is the formula used;

=LET(QQ,UNIQUE(FILTER('Stock Cat criteria -  BOM Orde'!B:B,'Stock Cat criteria -  BOM Orde'!I:I="N/A")),FILTER('Stock Cat criteria -  BOM Orde'!B:I,ISNA(MATCH('Stock Cat criteria -  BOM Orde'!B:B,QQ,0))*('Stock Cat criteria -  BOM Orde'!B:B<>"")))

The only difference I have done is had the lookup cover the entire column as the original data may fluctuate in the number of rows depending on when the data is pulled and rather than hard coding in a limit thought this would be ok? Do you have any idea what could be causing it?

Thanks again

 

[Fluff]

I would advise against using whole column references as it can seriously slow your workbook down, just use a limit that is more than the number of rows of data, without going over the top.

If some codes are being removed they must have an "N/A" somewhere in col I