Manipulate and convert item qty data in Excel

Question

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	?

Got it @Ryan would you able to explain in plain english not Excel formula, how to calculate this quantity, it is not clear to me from the input data, how would you obtain this number. What are the conversion factors to obtain it. Please provide for your input data, the expected output for NewQty. Thanks — David Leal, Nov 18 '22 at 19:40

David Leal · Accepted Answer · 2022-11-29T19:57:36.647

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:

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.

Manipulate and convert item qty data in Excel

1 Answers1