Hello,
I am trying to create a formula to generate a number based on the values of three status columns.
“Lane,” “Rack” and “Shelf” are status columns. “Slot” is a numbers column. I need “Slot” to be a formula column instead with the following criteria: Each time there is a new combination of the three status columns, “Slot” starts from 1 and increases by 1 until there is a new combination of the three status columns (one or more of the status columns’ values change). So the “Slot” column iteratively increases as long as successive items have the same combination. If an item in that iteration is removed, the next item with the same combination should decrease by one so that there is no empty value in between.
For example: “Lane” is ‘M,’ “Rack” is ‘3,’ “Shelf” is ‘2.’ This combination is the same for three straight items. The “Slot” number is 1, 2, 3 respectively. The item with slot number 2 is removed, so there is now only two items with the same combination. The “Slot” numbers of these items need to be 1, 2 respectively instead of 1, 3 respectively. Is such a formula possible? If so, how can it be implemented? Please refer to the image below to see an example of a combination.
Here is what I have so far:
IF({Lane}&{Rack}&{Shelf}=PREVIOUS({Lane}&{Rack}&{Shelf}),
IF({Slot}=PREVIOUS({Slot}),PREVIOUS({Slot})-1,PREVIOUS({Slot})+1),1)