Finding the highest revision levels in a BoM

[DazCD]

Good morning everyone:

I'm trying to filter on a HUGE sheet to find the highest revision for a parent code using the following formula:

=FILTER(INDEX('[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$K, SEQUENCE(ROWS('[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A)), {1,2,4,5,11}), '[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A=R2) (The top level code is being typed into R2)

Which returns the following:

Product Code	Parent Description	BoM Child	Child Description	Method
CD650017	180g Teeny Tiny Turtles 2025	N600186	3kg Bubble Gum Teeny Tiny Turtles	1
CD650017	180g Teeny Tiny Turtles 2025	N98427	Compostable SS Bag 76x114x180 CRIMP N006	1
CD650017	180g Teeny Tiny Turtles 2025	Y000323	Plain Label 60 x 65 Rectangle (1227)	1
CD650017	180g Teeny Tiny Turtles 2025	FLCD100001	%+Treat co Logo Label 2025+%	1
CD650017	180g Teeny Tiny Turtles 2025	PB000445	100mm x 90mtrs Direct Thermal Lab Media Perm Adhesive	1
CD650017	180g Teeny Tiny Turtles 2025	BX001647	APL214402 SRP Outer Box Sweets	1
CD650017	180g Teeny Tiny Turtles 2025	BX001647	APL214402 SRP Outer Box Sweets	2
CD650017	180g Teeny Tiny Turtles 2025	FLCD100001	%+Treat co Logo Label 2025+%	2
CD650017	180g Teeny Tiny Turtles 2025	N600186	3kg Bubble Gum Teeny Tiny Turtles	2
CD650017	180g Teeny Tiny Turtles 2025	N98427	Compostable SS Bag 76x114x180 CRIMP N006	2
CD650017	180g Teeny Tiny Turtles 2025	Y000323	Plain Label 60 x 65 Rectangle (1227)	2
CD650017	180g Teeny Tiny Turtles 2025	PB000019	76mm x 76mm White Thermal Box Label	2
CD650017	180g Teeny Tiny Turtles 2025	BX001647	APL214402 SRP Outer Box Sweets	3
CD650017	180g Teeny Tiny Turtles 2025	FLCD100001	%+Treat co Logo Label 2025+%	3
CD650017	180g Teeny Tiny Turtles 2025	N98427	Compostable SS Bag 76x114x180 CRIMP N006	3
CD650017	180g Teeny Tiny Turtles 2025	PB000019	76mm x 76mm White Thermal Box Label	3
CD650017	180g Teeny Tiny Turtles 2025	Y000323	Plain Label 60 x 65 Rectangle (1227)	3
CD650017	180g Teeny Tiny Turtles 2025	Y000338	Teeny Tiny Turtles (3kg)	3
CD650017	180g Teeny Tiny Turtles 2025	BX001647	APL214402 SRP Outer Box Sweets	4
CD650017	180g Teeny Tiny Turtles 2025	FLCD100001	%+Treat co Logo Label 2025+%	4
CD650017	180g Teeny Tiny Turtles 2025	PB000019	76mm x 76mm White Thermal Box Label	4
CD650017	180g Teeny Tiny Turtles 2025	Y000323	Plain Label 60 x 65 Rectangle (1227)	4
CD650017	180g Teeny Tiny Turtles 2025	Y000338	Teeny Tiny Turtles (3kg)	4
CD650017	180g Teeny Tiny Turtles 2025	X000024	75x114x160 Gussetted Cello Bag New	4

However, As you can see, it's returning multiple BoM revisions (the 'Method'), I only want the highest number revision to appear, in this case all rows associated with '4', i've been messing with it for hours but can't figure it out.

Could any one kindly assist?

 

[DazCD]

Ok, you need to change the 5 to a 6 in the 2 INDEX functions at the end of the formula

I did try this, but it returns a #CALC Error. I really appreciate your assistance with this btw!

 

[Fluff]

What is the current formula?

 

[Cubist]

I think your source data is TEXT.

 

[DazCD]

What is the current formula?

Okay some context:

Here are the Columns/Headers of the huge sheet i'm filtering data from (Columns A to K respectively):

bomparent

Parent Description

bompos

bomchild

Child Description

bomqty

uom

Child Type

FromDate

ToDate

baltmethod

This is the current formula, Since the "Baltmethod" (revision No), is in Column K/11, I changed the Index to 11. I tried 6 but didn't seem to want to work for me.

=LET(f,FILTER(INDEX('F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$L, SEQUENCE(ROWS('F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A)), {1,2,4,5,6,11}), 'F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A='1st page front (2)'!B4),FILTER(f,INDEX(f,,11)=MAX(INDEX(f,,11))))

 

[DazCD]

I think your source data is TEXT.

Interesting, it's an SQL set up pulling the data from our ERP system. I just ran an "ISNUMBER" formula on the the coumn, it does indeed return False.

 

[Fluff]

The last two Index functions need to be 6, not 11

 

[DazCD]

The last two Index functions need to be 6, not 11

Apologies, I did try both of them as 6, and didn't appear to work.

As cubist mentioned, my original data may appear to be text values.

 

[Fluff]

In that case you will to change the SQL to return numbers, rather than text.

 

[DazCD]

In that case you will to change the SQL to return numbers, rather than text.

=LET(
f, FILTER(
INDEX('F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$K, SEQUENCE(ROWS('F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A)), {1,2,4,5,6,11}),
'F:\NPD\[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A='1st page front (2)'!B4),
f_converted, IF(ISNUMBER(INDEX(f,,11)), INDEX(f,,6), VALUE(INDEX(f,,6))),
FILTER(f, f_converted = MAX(f_converted))
)

I actually tried running it through co pilot - it returned the above and appears to work. I'm unsure if it can be simplified though.

 

[Fluff]

You would be better off changing the query, so that it returns numbers.