How to Take Lowest Quantity from Child SKUs and set that number to Parent SKU-CodePudding

I'm trying to make a formula that will search amongst column D for parent (EX: 26013004) sku's child skus (EX: 26013004:26013004F, 26013004:26013004H, 26013004:R) then check for the lowest part quantity in stock.

So let's say 26013004:26013004F has a quantity of 8, 26013004:26013004H = 6, 26013004:26013004R = 5. Since the lowest value is 5, parent sku 26013004 quantity will be changed to 5.

How would I start for this formula?

| SKU                | Quantity |
|--------------------|----------|
| 26013004           | 5        |
| 26013004:26013004F | 8        |
| 26013004:26013004H | 6        |
| 26013004:26013004R | 5        |
| 26015002           | 5        |
| 31003002:31003002B | 2        |
| 31003002:31003002T | 3        |
| 31004001           | 0        |
| 31004002           | 4        |
| 31004002:31004002A | 5        |
| 31004002:31004002B | 5        |
| 31005001           | 4        |
| 31005001:31005001A | 8        |
| 31005001:31005001B | 2        |

EDIT: So far I thought about using =IFERROR(LEFT(A3, SEARCH(":", A3) -1), "") to generate a C column with parent skus for child products while parent products will be blank. Next step I was thinking of using vlookup to look through column C's with the parent skus and find the smallest quantity using MIN. I'm stuck with how to put this all into a formula.

EDIT 2: I just realized some of the data will have the child sku's written without the PARENTSKU:CHILDSKU(A) so looking up for both options would be nice. Otherwise use this table as an example:

| SKU       | Quantity |
|-----------|----------|
| 26013004  | 5        |
| 26013004F | 8        |
| 26013004H | 6        |
| 26013004R | 5        |
| 26015002  | 5        |
| 31003002B | 2        |
| 31003002T | 3        |
| 31004001  | 0        |
| 31004002  | 4        |
| 31004002A | 5        |
| 31004002B | 5        |
| 31005001  | 4        |
| 31005001A | 8        |
| 31005001B | 2        |

CodePudding user response：

If you have Excel 365, you can use Map to check each SKU and find the min matching quantity:

=IFERROR(MAP(A3:A16,LAMBDA(r,MIN(FILTER(B3:B16,LEFT(A3:A16,9)=r&":")))),B3:B16)

In Excel 2007, maybe you could add helper columns as shown in the centre of the image below, sort on SKU and quantity, then the min quantity would just be in the next row:

CodePudding user response：

Two solutions proposed:

1st uses two helper columns
2nd Uses two helper columns and a PivotTable

Both solutions have the data located at [B6:C20]

Parent (this column is common to both solutions)
Enter this formula at [D7], then copy it to [D8:D20]

= IFERROR( LEFT( $B7, -1  SEARCH( ":", $B7 ) ), TEXT(,) )

Minimum quantity
Enter this formula at [F7], then copy it to [F8:F20]

= IF( LEN($D7), $C7,
 IF( COUNTIF( $D$7:$D$20, $B7 ) = 0, $C7,
 MIN( IFERROR( $C$7:$C$20 / (( $D$7:$D$20 = $B7 )*1), TEXT(,) ) ) ) )

That produces the following results:

The formulas above should work in Excel 2007, just in case here is the second solution ...

PivotTable Insert a pivotbale in [J5] using the range [B6:D20] as SourceData

Minimum quantity
Enter this formula at [H7], then copy it to [H8:H20]

= IF( LEN($D7), $C7,
 IFERROR( VLOOKUP( $B7, $J$6:$K$13, 2, 0 ), $C7 ) )

That produces the following results:

Please try and let me know of any isuess\questions you may have.