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:
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?
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?
thank you Fluff!
