Shift Distinct Rows to New Column

[AngelaO]

Question:I have a need to take the distinct rowvalues and convert them into a new column.

Original:

Output:

URL in a different column for each of the records below.

Answer:

See the attached v11.3 workflow.

Steps:

1. Filter out Null rows and rows with "URL by County" in the F3 column

2. Deselect Null columns

3. Shift the URL to the F2 Column where F2 is Null

4. Replace numbers in the F2 column with the URL above

5. Remove unnecessary rows

[Joe_Mako]

Another route is to take advantage of the metadata, particularly the "F2" field. When F2 is Null it is either a header label or a URL value, and when it is not null (has a value) it is a record we want to keep.

With that information we can do this in three tools:

1. Multi-Row Formula to create a new field named "URL" and fill down the URL with the expression:

IF IsNull([F2]) THEN [F3] ELSE [Row-1:URL] ENDIF

2. Fillter to keep records where F2 is not Null, could also use !IsEmpty([F2])
3. Select fields to keep, rename, and set data type

Of course each situation is different, it can be useful to look at the data around the data of interest to see if that metadata can be used to manipulate the data of interest.