Complex data wrangling - VLOOKUP and IF formula

[Liline008]

Hi all,

I have this data challenge, and nobody seems to be able to find a way on how to automate it in Alteryx.

It is possible via Excel (involving Vlookups, some IF formulas, and lots of dragging down formulas), and I'd like to impress my Alteryx sceptical colleagues by coming up with an automated workflow.

So here's the challenge:

- The data in white is an extract from one of our systems. The merged cells aren't great, but that's an easy one to fix. 

- What I am trying to achieve is to get "Staff code", "Period" and "Grade" next to the charged hours (I have manually done it in the yellow cells)

- So the cells in yellow are blank in the original extract, and I would like to wrangle the data to get it populated via a workflow (the desired end result is in tabsheet "Desired output").

Note that the original extract is several thousands of lines.

Seems an easy one at first sight, but many have struggled with it before.

Hopefully this community can astonish me once again?

[JoeS · Accepted solution]

Hi @Liline008 

Took a few steps, but each on them I think you'll find logical to the manual steps you make in Excel.

But now it's forever automated in Alteryx.

Let me know if you have any questions

[Liline008]

Wow, I don't really have words for this... this is exactly what I need!

I think it took me longer to understand your solution, than you creating this workflow. Thanks so much!

One question for my own learning: can you explain how you used the "dynamic select" tool? 

I cannot figure out what your expression does (Left([Name],5) != "Field")

This question confirms three things:

- This community is great;

- Alteryx is great;

- I still have a lot of learning to go through!

[RolandSchubert]

Hi @Liline008,

if you can do it with Excel, you can (almost) do it with Alteryx. Some Multi-Row fomula tools do the job ... Sample workflow attached.

Best regards

Roland

[JoeS]

---

@Liline008 wrote:

Wow, I don't really have words for this... this is exactly what I need!

I think it took me longer to understand your solution, than you creating this workflow. Thanks so much!

One question for my own learning: can you explain how you used the "dynamic select" tool? 

I cannot figure out what your expression does (Left([Name],5) != "Field")

This question confirms three things:

- This community is great;

- Alteryx is great;

- I still have a lot of learning to go through!

---

You're welcome, I love these sort of challenges 🙂

Glad you were able to work most of it out, the dynamic select is in there to remove a lot of blank columns in one set.

saying the left 5 characters of the field name is not equal to "Field" as that's what all of the blank ones started with.