Long Excel Function Conversion

Question

Hi!

I am trying to convert a very long and complicated excel function into Alteryx. The actual function looks like this: ("=TEXT(BC2,""00000"")&""-""&BB2&"" ""&IF(SUMIFS(L:L,BC:BC,BC2,BB:BB,BB2,H:H,H2)>=0,1010,2010)&""-""&" & _ "VLOOKUP(BS2,GLProductCode!A:E," & "IF(IF(AI2>VLOOKUP(BS2,GLProductCode!A:E,5,FALSE),""LT"",""ST"")=""LT"",2,3),FALSE)&""-""&" & _ "VLOOKUP(BT2,GLProductCode!A:E,4,FALSE)&""-""&" & _ "VLOOKUP(VLOOKUP(BH2,LegalEntity!A:Q,8,FALSE),GLProductCode!G:H,2,FALSE)&""-""&K2&""-0000""")

To break it down into simple steps I believe this formula is saying: Change column BC to text. Sum column L if column BC contains value in cell BC2, column BB contains value in cell BB2, and column H contains value in cell H2. If the sumif (above) is >= 0, then "1010", else "2010". Vlookup col BS in sheet "GL Product Code" col A and IF col AI is > (vlookup col BS in col A of "GL Product Code" and return column E), then "LT" else "ST". If result of formula above is "LT" then "2", else "3". Vlookup col BT in sheet "GL Product Code" col A and return column D. Vlookup BH in sheet "Legal Entity" A and return column H. Then, vlookup column BH (with new value H) in col G of "GL Product Code" sheet and return column H. Finally, add col BC, BB, sumif col, BS, BT, and BH with "-" between each col.

I am not sure how to approach this in Alteryx or break it down into steps that can be preformed in Alteryx.

Any help would be appreciated! Thank you!

NicoleJohnson · Accepted Answer

Conversion of Excel formulas is always a fun exercise 🙂 The key to this one is going to be to identify your aggregation and Vlookup activities first and tackle those with Join tools (or Find & Replace if you prefer) and Summarize tool for the SUMIFS portion. Once you've done all your lookups, you can concatenate all the necessary fields together with a Formula tool, including a PadLeft formula to help get your first value in the right format (5 digits with leading zeroes if necessary).

I've attached an example with some dummy data that will hopefully help show you one possible way to tackle each of these components... take a look at some of the interactive trainings in the Alteryx Academy section as well, particularly those in the collection for "Alteryx for Excel Users" - there are some great demo videos that talk through a bunch of these basic Excel functions and how to do the same thing in Alteryx!

Cheers!

NJ