How to remove next repeated value in each row and replace blanks

[Roger2]

Similar to this issue posted [How to remove next repeated value in each row](Sorry cannot post links) I want to remove dupes among the columns for each row, however in my dataset, the repeated columns may not be at the end. Here's an example:

You'll see some duplicates between columns that I want to remove, and promote the names further down the columnset to fill in the blanks. In the first row as an example, I want L3 = Mary, L4 = John, L5 = Don, etc

I got as far as doing the following, but it appears this only works if the repeats are at the end of the line, not the middle. I get gaps with this method:

1. Transpose columns to rows with ID as key

2. Use sample tool to keep only 1st value of every ID

3. Sort ascending based on id and col name to get in order

4. Crosstab it back to table with ID as key

What step am I missing to get it to fill in the blanks left over?

Thanks!

[binuacs · Accepted solution]

@Roger2 you are almost there, you need few more tools to get it done

[Roger2]

Thank you! This worked like a charm.