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

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.

Add new column.xls

Transformation

Accepted answers

AZuc

@sean959 , see if this works for you

States in Order.yxzp

All comments

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?

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

@sean959 , see if this works for you

States in Order.yxzp

sean959

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703