replace filter

[ajm]

i have inherited a report that uses FILTER to transform the raw data input into a meaningful report. The original report used set ranges for each filter formula and these had to be expanded each time new data was added. to get around this, i changed all the ranges to dynamic named ranges (using indirect). the file is ballooning in size each month as its updated. Before FILTER, i would have run formulas adjacent to a pivot table (getting unique data from a list of multiples). This would probably still result in a large file but would it be as large?? any ideas on better method folks?

 

[ajm]

TRIMRANGE is unknown on my version of Excel.

 

[Peter_SSs]

TRIMRANGE is unknown on my version of Excel.

I thought that may be the case, that's why I said..

If you do not already have the TRIMRANGE function you might choose to wait a bit longer before deciding to change

The function is new and is only slowly being released to users. I was suggesting it in case you preferred not to immediately head off in a different direction to try to solve your problem but instead would rather wait.

 

[ajm]

Hi ajm,

Based on my experience, filters and formulas end-up eating your memory in large file. If you use Excel as à huge database, I would recommand thinking about vba. We could take a look if you have some example or dummy data if there is something to do before going towards vba.

ok, so i think VBA might be the best method. the file is so unwieldy.

its a simple report based on an export from our ERP. it identifies instances when purchase orders are created after invoices for products are received. Usual order is Purch Order first, then invoice.

the report, which consists of payment lines, is pasted into a Data tab at E1 (I actually paste only the data without the headers so i don't have to recreate named ranges each time. There are four helper rows in columns A-D but these could be incorporated into the code.

Column A (1st helper) flags line items where the invoice has been presented first. If the days between PO date and Invoice date are negative (Inv Date - PO Date), flag is 1, otherwise 0.
Column B (2nd helper) flags those line items posted against a purchase order (irrespective of when). If Column Y (Purchase Order Nbr) is blank, then "", else 1.
Column C 3rd helper) converts the payment month (from Payment Date) to a number 1 - 12. (i used this to order the columns displaying payments against POs)
Column D isolates the Payment Month.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Order After Invoice?	Total Orders	Month Order	Payment Month	Ledger Name	Account Number	Account Description	Invoice Date	Payment Date	Payment Amount (ex)	Payment Amount (inc)	Purchase Order	Purchase Order Create Date	Days between Order Date and Invoice Date
2	1	1	8	Feb	APACT	12345	Jim	30/01/2025	11/02/2025	55	55	2346	10/02/2025	-11
3	0	1	7	Jan	APACT	12345	Bob	19/01/2025	23/01/2025	42.1	42.1	9876	16/01/2025	3
4	0	0		Aug	APACT	56743	Randy	16/06/2024	17/08/2024	-91.4	-91.4
5	0	0		Jan	APACT	89765	Jurgen	30/01/2025	6/01/2025	91.4	91.4
6	0	0		Jan	APACT	33452	Lacy	19/01/2025	25/01/2025	-100	-100
7	0	1	6	Dec	APACT	99087	Sam	30/11/2024	16/12/2024	100	100	9345	1/11/2024	29
8	1	1	8	Feb	APACT	12345	Jim	27/12/2024	11/02/2025	55	55	2346	10/02/2025	-45
9	1	1	8	Feb	APACT	12345	Jim	5/01/2025	11/02/2025	55	55	2346	10/02/2025	-36
10	1	1	8	Feb	APACT	12345	Jim	6/01/2025	11/02/2025	55	55	2346	10/02/2025	-35
Sheet1

Cell Formulas
Range	Formula
A2:A10	A2	=IF(N2<0,1,0)
B2:B10	B2	=IF(L2="",,1)
C2:C10	C2	=IF(L2="","",IF(MONTH(I2)<6,MONTH(I2)+6,MONTH(I2)-6))
D2:D10	D2	=EOMONTH(I2,0)
N2:N3,N7:N10	N2	=H2-M2

There are two outputs from the data.

Firstly, a unique listing of all Purchase Orders and the monthly spend against each. POs listed down the page and monthly amounts across the page.
Secondly, a unique listing of each Purch Order that was created after its invoice, with monthly spend across the page.