Insert data in column, based on data in another column.

[JEM]

Hi, I hope some of you may be able to help me with a small problem I have encountered, and haven't found a way to solve so far.

As shown in the picture; 

My data currently looks like the upper example, and I am trying to turn it into the example below.

The accounting system puts the account number into the column as the date. I wish to separate the account number into a new column, as shown.

I have done this in Excel with VLOOKUP, however, as this is a recurring issue, I would prefer a flow in Alteryx.

I hope someone has an idea on how to perform this action, thank you!

 

[atcodedog05]

Hi @JEM 

can you provide the excel file we can help you out.

[atcodedog05]

Hi @JEM 

Here is how you can do it. With help of multi-row formula tool.

Workflow:

Hope this helps : )

[JEM]

Sure!

Please note; This is just a sketch I made for the sake of understanding how it's done. 

The actual data-set is approximately 5000 rows, but follows the same pattern

[atcodedog05 · Accepted solution]

Hi @JEM 

Please find the updated solution

Hope this helps : )

[JEM]

Thank you so much. I will give it a try and see if it works! 

[JEM]

It works very well, and is definitely what I am looking for! However, it appears that some of the "Text" fields further down are blank by accident, and therefore it inputs the date instead of the account no. Would you by any chance have an idea on how to fix this? Much appreciated!

[atcodedog05]

Hi @JEM 

Can you provide sample data on the issue and is your account number always completely numeric?

Happy to help : )

[JEM]

Yes, the account number is always completely numeric, 6 digits, no dashes.

 As you can see, when we get to Doc. No. 6, the "Text" is blank.

[atcodedog05 · Accepted solution]

Hi @JEM 

Here is a updated workflow

Hope this helps : )