Finding the highest revision levels in a BoM

D

DazCD

New Member
Joined
Aug 19, 2024
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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 CodeParent DescriptionBoM ChildChild DescriptionMethod
CD650017180g Teeny Tiny Turtles 2025N6001863kg Bubble Gum Teeny Tiny Turtles1
CD650017180g Teeny Tiny Turtles 2025N98427Compostable SS Bag 76x114x180 CRIMP N0061
CD650017180g Teeny Tiny Turtles 2025Y000323Plain Label 60 x 65 Rectangle (1227)1
CD650017180g Teeny Tiny Turtles 2025FLCD100001%+Treat co Logo Label 2025+%1
CD650017180g Teeny Tiny Turtles 2025PB000445100mm x 90mtrs Direct Thermal Lab Media Perm Adhesive1
CD650017180g Teeny Tiny Turtles 2025BX001647APL214402 SRP Outer Box Sweets1
CD650017180g Teeny Tiny Turtles 2025BX001647APL214402 SRP Outer Box Sweets2
CD650017180g Teeny Tiny Turtles 2025FLCD100001%+Treat co Logo Label 2025+%2
CD650017180g Teeny Tiny Turtles 2025N6001863kg Bubble Gum Teeny Tiny Turtles2
CD650017180g Teeny Tiny Turtles 2025N98427Compostable SS Bag 76x114x180 CRIMP N0062
CD650017180g Teeny Tiny Turtles 2025Y000323Plain Label 60 x 65 Rectangle (1227)2
CD650017180g Teeny Tiny Turtles 2025PB00001976mm x 76mm White Thermal Box Label2
CD650017180g Teeny Tiny Turtles 2025BX001647APL214402 SRP Outer Box Sweets3
CD650017180g Teeny Tiny Turtles 2025FLCD100001%+Treat co Logo Label 2025+%3
CD650017180g Teeny Tiny Turtles 2025N98427Compostable SS Bag 76x114x180 CRIMP N0063
CD650017180g Teeny Tiny Turtles 2025PB00001976mm x 76mm White Thermal Box Label3
CD650017180g Teeny Tiny Turtles 2025Y000323Plain Label 60 x 65 Rectangle (1227)3
CD650017180g Teeny Tiny Turtles 2025Y000338Teeny Tiny Turtles (3kg)3
CD650017180g Teeny Tiny Turtles 2025BX001647APL214402 SRP Outer Box Sweets4
CD650017180g Teeny Tiny Turtles 2025FLCD100001%+Treat co Logo Label 2025+%4
CD650017180g Teeny Tiny Turtles 2025PB00001976mm x 76mm White Thermal Box Label4
CD650017180g Teeny Tiny Turtles 2025Y000323Plain Label 60 x 65 Rectangle (1227)4
CD650017180g Teeny Tiny Turtles 2025Y000338Teeny Tiny Turtles (3kg)4
CD650017180g Teeny Tiny Turtles 2025X00002475x114x160 Gussetted Cello Bag New4




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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
How about
Excel Formula: 
=LET(f,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),FILTER(f,INDEX(f,,5)=MAX(INDEX(f,,5))))
 
Upvote 1
Solution
Apologies for the super late reply on this Fluff (work is manic right now).

I tried the formula you provided, it DOES pull through information but it still seems to pull through all revisions of the BoM. I'm out of my depth on this one so i'm not sure if there's any more useful information I could provide to you?

The only information I can think of is that the 'Method' is in Col K in the sheet i'm referencing. (The '11' column as per the Sequence(rows.
 
Upvote 0
This part
Excel Formula: 
FILTER(f,INDEX(f,,5)=MAX(INDEX(f,,5)))
should filter your original formula to only show those rows where the 5th col equals the max value in that column.
So not sure why it's not working for you.
 
Upvote 0
I don't think you need the INDEX and SEQUENCE part. Maybe:
Excel Formula: 
=FILTER(
    CHOOSECOLS('[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$K, 1, 2, 4, 5, 11),
    ('[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A = R2) *
    ('[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$K:$K =
        MAXIFS(
            '[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$K:$K,
            '[EFACS Data Downloads (BOM)..xlsx]BOM Parts'!$A:$A,
            R2
        )
    )
)
 
Upvote 0
That depends on whether the other workbook is always going to be open or not.
 
Upvote 0
None of the xxIF(S) functions work with closed workbooks, and choosecols will (sometimes) just return an array of 0s if the other workbook is closed.
 
Last edited:
Upvote 0
Fluff said:
This part
Excel Formula: 
FILTER(f,INDEX(f,,5)=MAX(INDEX(f,,5)))
should filter your original formula to only show those rows where the 5th col equals the max value in that column.
So not sure why it's not working for you.
Click to expand...

I did change the formula slightly for the addition of the "6th column" I'm not sure if that has anything to do with it? :
=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),FILTER(f,INDEX(f,,5)=MAX(INDEX(f,,5))))
Click to expand...

I've entered a Product code and am getting the following return, whereas ideally I would only want any rows associated with "3":

CD630077150g Creamy Vanilla All Butter Fudge 2024X00002475x114x160 Gussetted Cello Bag New181
CD630077150g Creamy Vanilla All Butter Fudge 2024PB00004245mm x 20mm Bar Code Peelable Label181
CD630077150g Creamy Vanilla All Butter Fudge 2024HLCD630077Header Label 150g Creamy Vanilla All butter Fdge 2020 V2181
CD630077150g Creamy Vanilla All Butter Fudge 2024HLCD630077Header Label 150g Creamy Vanilla All butter Fdge 2020 V2182
CD630077150g Creamy Vanilla All Butter Fudge 2024X00002475x114x160 Gussetted Cello Bag New182
CD630077150g Creamy Vanilla All Butter Fudge 2024Z00071830x20mm Sticker Barcode White Mid Gloss Perm Adhes LPR 5000182
CD630077150g Creamy Vanilla All Butter Fudge 2024HLCD630077Header Label 150g Creamy Vanilla All butter Fdge 2020 V2183
CD630077150g Creamy Vanilla All Butter Fudge 2024X00002475x114x160 Gussetted Cello Bag New183
CD630077150g Creamy Vanilla All Butter Fudge 2024Z00071830x20mm Sticker Barcode White Mid Gloss Perm Adhes LPR 5000183
 
Upvote 0
Ok, you need to change the 5 to a 6 in the 2 INDEX functions at the end of the formula
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top