Search code examples
exceldata-manipulation

Manipulate and convert item qty data in Excel

I need to calculate the quantity of a new item, given conversion factors of the original item data. the 'NewQty' column is what I'm trying to solve for. Is there a formula in Excel that would perform this entire calculation?

This might help- from another post, breaking out the UOM string columns has been solved, linked here.

The first row for example: OrigItem has 25 CA's. You first want to convert it to its lowest Unit of Measure (EA). 'OrigUOMString' says that there are 30 EA's in a CA, so step 1: 25 (OrigQty) * 30 (OrigUOMString) = 750 EA

For the new item, there are 40 EA's in a CA, so step 2: 750 / 40 (NewUOMString) = 18.75

So the result says that qty of the new item would be 18.5 CA, which is the result that would go into 'NewQty'.

The second row for example: 13 BX's, so 12 (from OrigUOMString) * 13 (OrigQty) = 156 EA's

156 / 40 CA (NewUOMString) = 3.9 CA's (which goes into column 'NewQty')

OrigItem# OrigUnitOfMeasure OrigQty OrigUOMString NewItem# NewUOMString NewUOM NewQty
111xy CA 25 1EA/2PK/12BX/30CA ABC123 1EA/4PK/20BX/40CA CA ?
111xy BX 13 1EA/2PK/12BX/30CA ABC123 1EA/4PK/20BX/40CA CA ?
Solution

  • In cell H2 you can try the following (I assume you don't have any excel version constraint per tags listed in the question)

    =LET(rng, A2:G3, oUnit, INDEX(rng,,2), oQTY, INDEX(rng,,3),
  oStr, INDEX(rng,,4), nStr, INDEX(rng,, 6), nUnit, INDEX(rng,,7),
  GET_UNIT, LAMBDA(x,unit, 1*TEXTAFTER(TEXTBEFORE(x, unit), "/", -1)),
  MAP(oUnit, oQTY, oStr, nStr, nUnit,LAMBDA(oUn,qty,oSt,nSt,nUn,
    LET(m, GET_UNIT(oSt, oUn), d, GET_UNIT(nSt, nUn),
      (qty * m)/d)))
)

    and here is the output: Sample excel file

    The main idea is to create a LAMBDA function (because we are going to use it more than once): GET_UNIT to extract the unit information. We extract the text before the unit via TEXTBEFORE, and then we get the sub-string after the last instance of / via TEXTAFTER. The rest is just to use MAP to iterate over all rows and then use the multiplier (m) and the divisor (d) to do the calculation.