Rearranging Rows based on a key value

tarnold

I want to add a sequence number to each of these rows the first row always has a 0 for Prev Logical Device and the last row always has a 0 for Next Logical Device.  Pretty easy to add the sequence number for the first row. How do I go through a list adding an incremental sequence number for the correct row.  You will see that row 1 under the headings is the first row but the next sequence number would actually be row 5.  Since the device numbers are not sequential sorting doesn't help except to find the first row.  Any help would be appreciated.

Sample.xlsx

danilang

Hi @tarnold 

In order to accomplish this, you need to iterate through the list several times.  First to find the starting device, then to find the 2nd device, etc.  In a macro-free workflow you could accomplish this by joining the list to itself 7 times to find the sequence, but you'd have to manually modify the workflow if you have a sequence with 8 devices.      

Luckily, you can solve this using an iterative macro that steps through the list once for every record.  

The macro itself is about as simple as an iterative macro can be.  The filter sends the Target device out the Data output.  The Append Fields tool takes the next ID from the selected record and add that to the remaining records as the new TargetDeviceID and sends the remaining to the IterativeOutput that feeds back into the IterativeInput.  Each iteration removes one row from the remaining rows and stops when there are no remaining records or 1000 iterations, which ever comes first

The main workflow just sets things up to pass to the macro and adds the sequence ID to records

The 2 browse tools give you the output sorted by sequence id or the original id

Dan

Workflow.yxzp