Designer - how to add a new column for when a value repeats?

[sean959]

I am trying to figure out how to add a column in Alteryx based on when it finds the first value of something. For example in the attached have the first value be Alabama in the new column and then after the first value is found equal Alaska and so on I have a lookup but am not sure how to get this configured in Alteryx. I have not seen anything similar to this before. 

[cmcclellan]

A multi-row formula would solve this 🙂 but how do you know that it starts with Alabama ?

[sean959]

Its the way the report pulls I select the states and so I select AL and AK then all of then the All Tax Types Sub-Totals are for Alabama for the first one and then the second ones are Alaska and so on. I pull it every month so I know exactly which ones are 1st/2nd and then last so I could do a vlookup for that? 

[sean959]

by vlookup I mean join but I dont get how the multi-row would solution for it. 

[cmcclellan]

Yeah, it's tricky when the list of states will change all the time.

[sean959]

So it is actually the same 12 states every month I pull it. I want to be able to change the state to all values before the first All tax types sub-total = Alabama and 2nd = Alaska third = x. The states organization will not change every month

[AZuc · Accepted solution]

@sean959 , see if this works for you

[sean959]

Thank you Andre! That is exactly what I was looking for!