Hi Everyone,
So basically I have a dataset like the shown below.
So basically If I search for Production BOM No. N-10CKD50B-904525 Then It will show me 70CKD05-904, then if I will search 70CKD05-904 in Production BOM No. then it will show me 70CKD04-904 in No. like in stages. Then if I search 70CKD04-904 in Production BOM No. then it will show 70CKD03-904 in No.
So the both columns are interlinked Production BOM No. and No.
So I wanted that if I search for Production BOM NO. it shows me all the data at once of all the Stages.
Then I created this formula
=VSTACK(
FILTER(
CHOOSECOLS(Table3[[#All],[Production BOM No.]:[Scrap %]],1,5,6,8,9),
(Table3[[#All],[Production BOM No.]] = Samples2!$C$1) * (LEFT(Table3[[#All],[No.]], 2) <> "70")
),
FILTER(
CHOOSECOLS(Table3[[#All],[Production BOM No.]:[Scrap %]],1,5,6,8,9),
ISNUMBER(SEARCH(CONCAT(70,MID($C$1,5,3)), Table3[[#All],[Production BOM No.]])) * (RIGHT(Table3[[#All],[Production BOM No.]],3) = RIGHT($C$1,3)) * (LEFT(Table3[[#All],[No.]], 2) <> "70")
)
Where basically if I put the BOM No. in C1 I get all the data. and for the stages its second filter where I am taking first few digits and last 3 digits to match.
but now my main concern is this works if i am looking for BOM No. one at the time but now i want to look for 100 bom no. at once
So basically I have a dataset like the shown below.
| Production BOM No. | Line No. | Version Code | Type | No. | Description | Unit of Measure Code | Quantity |
| N-10CKD50B-904525 | 10000 | B | Item | 70CKD05-904 | BBT FSJ SSJSS | HL | 0.078 |
| N-10CKD50B-904525 | 20000 | B | Item | 4001545 | BSDFSDGDSFS | NO | 3.62 |
| N-10CKD50B-904525 | 30000 | B | Item | 4001149 | CRSGSDGSDX | NO | 12 |
| N-10CKD50B-904525 | 40000 | B | Item | 4001360 | GSDND SJKS | NO | 12 |
| N-10CKD50B-904525 | 50000 | B | Item | 4001169 | CaSFD SDWS | NO | 1 |
| N-10CKD50B-904525 | 60000 | B | Item | 4001543 | BSFS JSD ASFI | NO | 8.38 |
| N-10CKD50B-904525 | 70000 | B | Item | 4001361 | LABEL NYD | NO | 12 |
| N-10CKD50B-904525 | 80000 | B | Item | 4001362 | LABEL NYD | NO | 12 |
| N-10CKD50B-904525 | 90000 | B | Item | 2000112 | CO2 | KG | 0.0108 |
| N-10MDD1330B-401401 | 10000 | B | Item | 70MDD05-401 | BBT-CB | HL | 0.0792 |
| N-10MDD1330B-401401 | 20000 | B | Item | 4001544 | Bottle-N | NO | 8.85 |
| N-10MDD1330B-401401 | 30000 | B | Item | 4001152 | CROWN-NY | NO | 24 |
| N-10MDD1330B-401401 | 40000 | B | Item | 4001172 | Carton C | NO | 1 |
| N-10MDD1330B-401401 | 50000 | B | Item | 4001542 | Bottle-Re | NO | 15.15 |
So basically If I search for Production BOM No. N-10CKD50B-904525 Then It will show me 70CKD05-904, then if I will search 70CKD05-904 in Production BOM No. then it will show me 70CKD04-904 in No. like in stages. Then if I search 70CKD04-904 in Production BOM No. then it will show 70CKD03-904 in No.
So the both columns are interlinked Production BOM No. and No.
So I wanted that if I search for Production BOM NO. it shows me all the data at once of all the Stages.
Then I created this formula
=VSTACK(
FILTER(
CHOOSECOLS(Table3[[#All],[Production BOM No.]:[Scrap %]],1,5,6,8,9),
(Table3[[#All],[Production BOM No.]] = Samples2!$C$1) * (LEFT(Table3[[#All],[No.]], 2) <> "70")
),
FILTER(
CHOOSECOLS(Table3[[#All],[Production BOM No.]:[Scrap %]],1,5,6,8,9),
ISNUMBER(SEARCH(CONCAT(70,MID($C$1,5,3)), Table3[[#All],[Production BOM No.]])) * (RIGHT(Table3[[#All],[Production BOM No.]],3) = RIGHT($C$1,3)) * (LEFT(Table3[[#All],[No.]], 2) <> "70")
)
Where basically if I put the BOM No. in C1 I get all the data. and for the stages its second filter where I am taking first few digits and last 3 digits to match.
but now my main concern is this works if i am looking for BOM No. one at the time but now i want to look for 100 bom no. at once
