Move / Update Multiple Fields At Once

[AndrewKwast]

I have a sets of data where the order in which the codes are entered matter, that is to say, column 1 should always contain the first code, column 2 should always contain the second code, etc. However, the code itself takes up three columns, so that means I need to update three columns at once if I want to move the codes around.

Below is a sample of what I am trying to accomplish. I want to move all the values to the left so that Code 1 fields are always filled before Code 2 fields, and Code 2 fields are always filled before Code 3 fields.

I have considered using a bunch of expressions in the formula tool but I was hoping there could be an easier way. I have also considered using the multi-field formula tool, but I don't think it works since each field is updated based on a different expression.

Before:

TYPE1	CODE1	MISC1	TYPE2	CODE2	MISC2	TYPE3	CODE3	MISC3
A	370	39	B	909	18	H	311	56
B	307	67				K	630	15
			G	663	57	M	172	42
						J	454	91
B	813	60
D	105	91	B	957	76

Desired Result:

TYPE1	CODE1	MISC1	TYPE2	CODE2	MISC2	TYPE3	CODE3	MISC3
A	370	39	B	909	18	H	311	56
B	307	67	K	630	15
G	663	57	M	172	42
J	454	91
B	813	60
D	105	91	B	957	76

Let me know your thoughts! Thanks!

[MarqueeCrew · Accepted solution]

@AndrewKwast,

@BenMoss answered a "shift left" post https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Shift-all-data-to-the-left-if-there-is... the other day that describes a solution similar, if not equal to what I would recommend.  

Please try it and let's see if it doesn't do exactly what you want.

Cheers,

Mark

[AndrewKwast]

What if the MISC code below is optional?

In this case, the space in the third position may be correct.

How would the solution change in this case to ensure that, if the Type and Code are populated, the Misc code is ok blank?

Before:

TYPE1	CODE1	MISC1	TYPE2	CODE2	MISC2	TYPE3	CODE3	MISC3
A	370	39	B	909		H	311	56
B	307					K	630	15
			G	663	57	M	172	42
						J	454	91
B	813	60
D	105		B	957	76

After:

TYPE1	CODE1	MISC1	TYPE2	CODE2	MISC2	TYPE3	CODE3	MISC3
A	370	39	B	909		H	311	56
B	307		K	630	15
G	663	57	M	172	42
J	454	91
B	813	60
D	105		B	957	76

[AndrewKwast]

I did a quick fix by adding then removing a placeholder for the optional third value.

E.g.

TYPE1	CODE1	MISC1	TYPE2	CODE2	MISC2	TYPE3	CODE3	MISC3
A	370	39	B	909	***	H	311	56
B	307	***	K	630	15
G	663	57	M	172	42
J	454	91
B	813	60
D	105	***	B	957	76