Formula Question Regarding Switching Number to new Column

[kpontarollo]

I am looking for help on the following:

I need to write a formula that moves the number over to a different column then it is supposed to be in. What I have highlighted in yellow are the numbers that need to switch from the column "auto open" to the column "total open." The tricky part is the FIRST "1" in auto-open by a specific person (Kelsey) is fine, but anything after with the name Kelsey should be under the column "total open." Down below you see that the 1 under the first Kelsey is green, that 1 can stay there, the one in yellow below, should be in "total open column" and even further you see 1 in the total open column with the name Kelsey because those were done correctly.

In a nutshell, the first "auto open" for a specific person, but any second email that is opened by that person should be considered a "total open"

[JagdeeshN]

@kpontarollo  The beauty of Alteryx is that it allows us to do the something in multiple ways.

Please find attached one of the way using a batch macro.

Hope this helps.

Best,

Jagdeesh 

[Pingu]

Hi @kpontarollo ,

The tile tool is pretty handy for this use case.

Attached an example.

[binuacs]

@kpontarollo Another way of doing this

[kpontarollo]

Hi Everyone! All of these worked great so far.... However, another issue came up. This fixes the problem to changing any auto-open to a total open when there are more than one auto opens. For example: it fixed. Auto Open, Auto Open, Total Open to Auto Open, Total Open, Total Open. WHICH IS GREAT- THANK YOU.

What it does not fix is the following: 

Total Open, Auto Open, Total Open.... Gets switched to Total Open, Total Open, Total Open... it should stay the exact same... I only need auto opens to become total opens when there are multiple after the first auto open.

So as another example:

Total Open, Auto Open, Auto Open, Total Open.... I would need to become.... Total Open, Auto Open, Total Open, Total Open... Does that make sense?!

[JagdeeshN]

@kpontarollo  Can you please post an example of the input and output just for better understanding

[kpontarollo]

@JagdeeshN yes of course! The first picture underneath is the original that I have before using the formulas. The highlighted green represents the FIRST auto-open. Where is is yellow, is where it is incorrect. The formulas provided above were great in fixing the yellow..... see picture 2. However, "John" where he had a total open, then an auto open, then a regular open, it changed his auto-open to the wrong position (now yellow in the second picture). 

In a nutshell, it does not matter how many "total opens" for a specific person there are before the "auto open" but after the "auto open" there can be no other "auto opens"

[JagdeeshN]

@kpontarollo  I think the attached solution puts the first occurrence into Auto Open and the rest into Total Open for each person. 

Hope this helps.