Changing Column Positions After Certain Occurence

[kpontarollo]

Hello Everyone, I tried to ask this question with no luck and I am hoping that a different way of explaining it is possible. Please see picture for reference. There can be as many "Total Opens" for one specific person, once the "Auto Open" happens for a specific person, the rest must become "Total Opens." Below, for Kelsey, the red "1" should be in the Total open column because after the first "auto- open" all must become total opens, for that specific person only. Under Kelsey, you can see Robyn, which has an accurate way of how it should be done. It does not matter that Total Opens came before the Auto Opens, just once that auto open does occur for that specific person, I need a formula that would change all other auto opens, to total opens. 

THANK YOU!

[mbarone]

Hi @kpontarollo ,

Have you tried the Multi-Row Formula tool (link taken from the Tool Mastery Index , which you should definitely bookmark).  Also, if you click on that tool on the Preparation tool pane, you'll see you can open an example of how to use the tool.

Try it out and see if you can't come up with a multi-row formula to get this done.  Let us know once you give it a few tries if you can't seem to get it (show us the formula(s) you tried).

[binuacs]

@kpontarollo Please provide the sample input file and expected output result as well in an excel?

[kpontarollo]

Hi!

This would be the input vs the output (input on the left, output on the right). It would be on a much bigger scale with multiple people listed.

[Pingu · Accepted solution]

Hi @kpontarollo 

2nd try. What about this with the tiling tool?

Hmm I tried to change the numbers, but it is still wrong. I will try some more as it is an interesting one.

Edit: What about this. It probably can be done in a lot less tools.

[kpontarollo]

@Pingu The tiling tool worked great for 98% of what I needed but just caused that one error- I appreciate you trying, I have been STUMPED for days.

[mbarone]

@kpontarollo - I strongly suggest you familiarize yourself with the Multi-Row Formula tool as I previously mentioned.  If you've been stumped for days, it will be well worth another half-day of really learning that tool.  Not only will it solve this problem with one tool, but it will come in handy in countless future endeavors.  Like I said, give it a go and if you get stuck show us what you've tried and we can help you through it.

[Pingu]

I edited my post so now it  should work if it starts with a auto open.

[Aguisande · Accepted solution]

@kpontarollo 

Here are my 2 cents:

I totally agree with @mbarone about the Multi-Row formula and how to allocate the time.

In this case, this is a 2 tool logic, being the Multi-Row Formula the core of the solving.

As you may see here, there are some other things added (like sorting the records prior of processing) to ensure that the use case is solved, no matter how you get the data (i.e. What happens when I receive scrambled data? What may happen if I get gaps between dates? .. etc..). Here, "Defensive Design" is your best friend.

The only use case it won't solve is if you have "Auto Opens" are not consecutive.

- Input Data:

- Output Data:

BTW: Just out of curiosity, shouldn't be the last Auto Open the one that remains as 1 in your Data?  In case it's the other way around, only changing a sign will accomplish it (Yes, that tool is THAT powerfull!) 

[kpontarollo]

@mbarone definitely have started looking at this tool. I have began to play around with it- still no expert on it yet! I appreciate the suggestion so much! I would have never thought to use this tool. Thanks!