Question about LET function

[jelmer123456]

Hi,

I have a formula with the LET function from this forum. I used it in a file and it worked fine, but now in a new file its having some issues. The first A to G works fine, but after that most values are placed some rows to the left. It should fill in the data if the text in row 1 matches which it does in the first part. Also it fills in zeroes when empty, while before it used to be empty (it should be empty)
Printscreens:

I tried uploading a mini sheet but it said I had to many cells selected (above 3000) while I had ~2000 selected, so if u know what causes that I can upload a mini sheet if needed.

 

[RoryA]

We don't need to see more than a small sample of the cells - I'd say 2000 was probably far too many.

 

[jelmer123456]

How about this: (A to AC will get filtered with kutools as a printable files based on colum D)

test file mrexcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T		V			Y				AC																																																		CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI
1	site_id	unit_id	section_id	drawing_id	stream_id	leak_equipment	leak_source	old_ppm_value	project_id	ppm_value	system	id	equipment	equipment_type	source	position	size	unit_of_measure	equipment_location	source_location		access_level			insulated				access																																																				rec_id	export_id	site_id	unit_id	section_id	drawing_id	stream_id	leak_equipment	leak_source	protocol	system	id	equipment	equipment_type	source	position	size	unit_of_measure	equipment_location	source_location	line	access_level	remark	memo	isolated	very_big	blind_request	corrosion	access	access_accesible	manufacturer	year	history_date
2	FTL	*NONE	*NONE	330-0055-0023	STR010	1	1	0	0	FL	BL	FL	0	10	IN	0	0	0	0	0		0			AC																																																								1995	1	FTL	*NONE	*NONE	330-0055-0023	STR010	1	1	PS			FL	BL	FL		10	IN				0			0	0	0	0	AC	TRUE		2020	#####
3	FTL	*NONE	*NONE	330-0055-0023	STR010	2	1	0	0	FL	BL	FL	0	6	IN	0	0	0	0	0		0			AC																																																								1996	1	FTL	*NONE	*NONE	330-0055-0023	STR010	2	1	PS			FL	BL	FL		6	IN				0			0	0	0	0	AC	TRUE		2020	#####
inquiry (31)

Cell Formulas
Range	Formula
A2:Z38	A2	=LET(Fltr,FILTER(CC2:DQ38,COUNTIF(A1:AD1,CC1:DQ1)),Sorted,SORTBY(FILTER(CC2:DQ38,COUNTIF(A1:AD1,CC1:DQ1)),MATCH(FILTER(CC1:DQ1,COUNTIF(A1:AD1,CC1:DQ1)),A1:AD1,0)),IF(ISBLANK("Sorted"),"",Sorted))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
'inquiry (31)'!_FilterDatabase	='inquiry (31)'!$A$1:$AC$1	A2
'inquiry (31)'!Print_Area	='inquiry (31)'!$A$1:$AC$8270	A2
'inquiry (31)'!Print_Titles	='inquiry (31)'!$1:$1	A2

 

[RoryA]

Why do you have quotes round Sorted in this part:

ISBLANK("Sorted")

(The word "Sorted" can't be blank!)

 

[jelmer123456]

Why do you have quotes round Sorted in this part:

ISBLANK("Sorted")

(The word "Sorted" can't be blank!)

No idea, but deleting the quotations marks fixed the random 0's so thats one problem fixed, thanks

 

[RoryA]

In the file where it does work, do you have any headers in the result table that are not in the source table? I think those would be the source of your problem, looking at the formula.

 

[jelmer123456]

Both files use the same source table and the file where it does work has more headers than this one. From my understanding from when I got the formula it would look for corresponding headers and copy the data thats under it from the source to the result table.

 

[RoryA]

As far as I can see (and I admit I haven't tested it) it will only return data from matching columns, but it doesn't leave gaps for columns that don't match, so your data won't line up properly after the first header that isn't in the source data.

 

[jelmer123456]

As far as I can see (and I admit I haven't tested it) it will only return data from matching columns, but it doesn't leave gaps for columns that don't match, so your data won't line up properly after the first header that isn't in the source data.

Looking at the order from both the source and result this makes sense. No idea how i'm gonna fix this though I didn't make the formula myself.

 

[RoryA]

Try something like this?

=LET(Data,INDEX(CC2:DQ38,SEQUENCE(37),MATCH(A1:AD1,CC1:DQ1,0)),IF(COUNTIF(CC1:DQ1,A1:AD1)>0,IF(Data="","",Data),""))